Re: Help regarding query

Try this. Not tested but I think it should work.

select

     a.name, nvl(b.status,'ZZ')
from

     (select distinct name from mytable) a,
     (select name, status from mytable where status =3D 'LI') b
where
     a.name =3D b.name(+)

--
Chuck Hamilton
QVC Inc.
Enterprise Technical Services
Oracle DBA


                                                                       =


                                           =20

                    "Atish Prasad                                      =


                                           =20

                    Das"                 To:     Multiple recipients of=
 list ORACLE-L <ORACLE-L_at_fatcity.com>      =20
                    <atish.das_at_wi        cc:     (bcc: CHUCK HAMILTON/Q=
VC)                                        =20
                    pro.com>             Subject:     Help regarding qu=
ery                                        =20
                    Ext: NA                                            =


                                           =20

                    Sent by:                                           =


                                           =20

                    root_at_fatcity.                                      =


                                           =20

                    com                                                =


                                           =20

                                                                       =


                                           =20

                                                                       =


                                           =20

                    05/26/00                                           =


                                           =20

                    06:14 AM                                           =


                                           =20

                    Please                                             =


                                           =20

                    respond to                                         =


                                           =20

                    ORACLE-L                                           =


                                           =20

                                                                       =


                                           =20

                                                                       =


                                           =20






Hi Gurus,

Can some one tell me how to do this?

Name=A0=A0=A0=A0  Status
----=A0=A0=A0=A0  ------
A=A0=A0=A0=A0=A0=A0=A0=A0 LI
A=A0=A0=A0=A0=A0=A0=A0=A0 DE
A=A0=A0=A0=A0=A0=A0=A0=A0=A0EX
B=A0=A0=A0=A0=A0=A0=A0=A0 DE
B=A0=A0=A0=A0=A0=A0=A0=A0 EX
C=A0=A0=A0=A0=A0=A0=A0=A0 LI
C=A0=A0=A0=A0=A0=A0=A0=A0 EX

In the above table, there can be only one  record per Name where the st=
atus
will be LI.
The query result should be like  this:

Result:

Name=A0=A0=A0=A0  Status
----=A0=A0=A0=A0  ------
A=A0=A0=A0=A0=A0=A0=A0=A0LI
B=A0=A0=A0=A0=A0=A0=A0  ZZ
C=A0=A0=A0=A0=A0=A0=A0  LI

i.e. If there is at least one occurence of  LI status, then the name sh=
ould
have LI as status. If there is no LI at all,  then also the Name should=

appear, but with a Status ZZ.