Re: Perform Arithmetic On Case Statement Decisions

On Feb 19, 9:20 am, "jim" <jimmorga..._at_gmail.com> wrote:
> Hi,
>
> I have a case statement that's kind of like this:
>
> select name,
>
> case when before = 'x' then 1
> when before = 'y' then 2
> else 3
> end as before_numeric,
>
> case when after = 'x' then 1
> when after = 'y' then 2
> else 3
> end as after_numeric
>
> from table
>
> This takes two columns of alphabetic strings and yields columns of
> numeric values (before and after values). I'm currently running the
> query and then doing the math in Excel after the fact (after minus
> before).
>
> Is there any way to perform a calculation on the output of a case
> statement *in* the select query itself? Ideally this would return
> another column containing the value of after-before.
>
> Thanks in advance.
>
> jim

Answer: YES

Oh you want details, why didn't you say so? 8^)

well you could put the case query in an inline view since you specified it HAD to be in the same SQL statement.

If your main query then gets too long, you could make the inline view into a real view.

If you consider allowing PL/SQL, then you could make a function that performs the case logic, converting characters to the appropriate values.

HTH,
   Ed Received on Tue Feb 20 2007 - 12:07:18 CST