Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: Perform Arithmetic On Case Statement Decisions

Re: Perform Arithmetic On Case Statement Decisions

From: Ed Prochak <edprochak_at_gmail.com>
Date: 20 Feb 2007 10:07:18 -0800
Message-ID: <1171994838.726617.179910@h3g2000cwc.googlegroups.com>


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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US