Re: SQL question

Ranga Chakravarthi wrote:

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

Keeping in mind that with 9i you can use it in procedures but in 8i only in SQL*Plus or as dynamic SQL.

Daniel Morgan Received on Fri Nov 15 2002 - 12:50:45 CST