SQL question

From: Murty Adavi <adavi_at_attbi.com>
Date: Fri, 15 Nov 2002 01:45:53 GMT
Message-ID: <llYA9.33999$1O2.2923@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 - 19:45:53 CST