Message-Id: <10511.106986@fatcity.com> From: Jane Gao Date: Mon, 29 May 2000 08:09:33 +0800 Subject: RE: Help regarding query This message is in MIME format. Since your mail reader does not understand this format, some or all of this message may not be legible. ------_=_NextPart_001_01BFC902.2E35313E Content-Type: text/plain Try this query: Select Name, decode(Status, 'LI', 'LI', 'ZZ') from yourtable Group by Name, decode(Status, 'LI', 'LI', 'ZZ') Cheers Jane Gao Senior Application Support Analyst Kalgoorlie Consolidated Gold Mines --------------------------------------------------------------------------- > * Phone : (08) 9022 1129 *Fax : (08) 9022 1149 > *Address : PMB 27 KALGOORLIE WA 6430 > *E-mail : jgao@kalgold.com.au > > > -----Original Message----- > From: Atish Prasad Das [SMTP:atish.das@wipro.com] > Sent: Fri, 26 May 2000 18:14 > To: Multiple recipients of list ORACLE-L > Subject: Help regarding query > > Hi Gurus, > > Can some one tell me how to do this? > > Name Status > ---- ------ > A LI > A DE > A EX > B DE > B EX > C LI > C EX > > In the above table, there can be only one record per Name where the status > will be LI. > The query result should be like this: > > Result: > > Name Status > ---- ------ > A LI > B ZZ > C LI > > i.e. If there is at least one occurence of LI status, then the name should > have LI as status. If there is no LI at all, then also the Name should > appear, but with a Status ZZ. > > Can somebody help in this regard soon? Thanks a lot.......... Atish. ------_=_NextPart_001_01BFC902.2E35313E Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable RE: Help regarding query

Try this = query:

Select Name, = decode(Status, 'LI', 'LI', 'ZZ') from yourtable
Group by Name, = decode(Status, 'LI', 'LI', 'ZZ')

Cheers

Jane Gao
Senior Application Support = Analyst
Kalgoorlie Consolidated Gold = Mines
---------------------------------------------------------= ------------------
( Phone :  (08) 9022 1129
.Fax    =         :  (08) 9022 = 1149
+Address        = :  PMB 27
        =             KALGOORLIE  WA  = 6430
*E-mail :  jgao@kalgold.com.au