Re: SQL question

"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))))
>

Why is it dirty looking? Looks pretty clean (and clear) to me.

>
> Thanks
> Murty
Received on Mon Nov 18 2002 - 10:36:55 CST