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

On Feb 19, 9:20 am, "jim" <jimmorga...@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

Thanks, Ed. So what would this look like inline? I'm an Oracle
novice, so elementary advice is appreciated.

Thanks again.

On Feb 21, 4:00 pm, "jim" <jimmorga...@gmail.com> wrote:
> Thanks, Ed. So what would this look like inline? I'm an Oracle
> novice, so elementary advice is appreciated.
>
> Thanks again.


select name, after_numeric - before_numeric
from (
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 )
;

Try that. It is an in-line view.

You really should read the manuals. I'll leave the other two options
for an exercise. They really aren't much harder that the above.
(HINTS: look up CREATE VIEW and CREATE FUNCTION for the second anfd
third option respectively)

Have a good read.
Ed