Re: SQL question

Murty,
If you are on 8i or higher, use CASE it s much clearer.

SELECT A, B, C, D,

       CASE WHEN A IS NULL OR
                 B IS NULL OR
                 C IS NULL OR
                 D IS NULL
            THEN E
            ELSE D

"Murty Adavi" <adavi_at_attbi.com> wrote in message news:llYA9.33999$1O2.2923_at_sccrnsc04...
> I have the following table
>
> create table Test(
> A numer(4) null,
> B number(4) null,
> C number(4) null,
> D number(4) null,
> X number(4) not null,
> Y number(4) not null)
>
> I need to write a SQL stmt that does the following....
> if (A is null) OR (B is null) OR (C is null) OR (D is null) I need to
> fetch A,B,C,D &E
> If all A,B,C & D are not null, I need to fetch A,B,C,D & F
>
>
> Eg. If the Test table contains ....
> ===============================
> A B C D E F
> ===============================
> NULL 2 3 4 10 20
> 3 NULL 4 NULL 30 40
> 2 3 6 7 50 60
> ===============================
>
> then my query results should be
> ================================
> A B C D
> ================================
> NULL 2 3 4 10
> 3 NULL 4 NULL 30
> 2 3 6 7 60
> =================================
>
> Is it possible to achieve this without using a dirty-looking nested
DECODE
> like......
> select a,b,c,d,
> decode(a,NULL,E,decode(b,NULL,E,decode(c,NULL,E,decode(d,NULL,E,F))))
>
>
> Thanks
> Murty
>
>
>
>
>
>
Received on Thu Nov 14 2002 - 21:08:46 CST