Re: Is it possible to subtract case statements

On Feb 14, 10:30 am, "MrHelpMe" <clintto..._at_hotmail.com> wrote:
> Hello all,
>
> I am wondering if it is possible to subtract case statements in oracle
> 9.2? If it is, could someone please show me how. This is basically
> what I am using for my case statements:
>
> [code]
>
> SELECT
> (Case when field1 = 'hello' then 1 else 0 END)Case1,
> (case when field2='test' then 1 else 0 END)Case2,
> FROM Table x;
>
> [/code]
>
> So I would get the resultset as such:
>
> Case1 Case2 Result
> 1 1 0
> 0 0 0
> 1 0 0
> 0 1 -1
>
> Any ideas. Thanks so much

The DECODE syntax is much more compact than that of the CASE syntax. For example:
SELECT
  DECODE(FIELD1,'hello',1,0) CASE1,
  DECODE(FIELD2,'test',1,0) CASE2
FROM
  X;

Now, what if you want to add the values, but do not want to repeat the formulas? Slide the above into an inline view: SELECT
  CASE1,
  CASE2,
  CASE1-CASE2 RESULT
FROM
  (SELECT
    DECODE(FIELD1,'hello',1,0) CASE1,
    DECODE(FIELD2,'test',1,0) CASE2
  FROM
    X);

Charles Hooper
PC Support Specialist
K&M Machine-Fabricating, Inc. Received on Wed Feb 14 2007 - 09:54:59 CST