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_at_kalgold.com.au
>
>
> -----Original Message-----
> From: Atish Prasad Das [SMTP:atish.das_at_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
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2//EN">
<HTML>
<HEAD>
<META HTTP-EQUIV=3D"Content-Type" CONTENT=3D"text/html; =
charset=3Diso-8859-1">
<META NAME=3D"Generator" CONTENT=3D"MS Exchange Server version =
5.5.2448.0">
<TITLE>RE: Help regarding query</TITLE>
</HEAD>
<BODY>
<P><FONT COLOR=3D"#0000FF" SIZE=3D2 FACE=3D"Arial">Try this =
query:</FONT>
</P>
<P><FONT COLOR=3D"#0000FF" SIZE=3D2 FACE=3D"Arial">Select Name, =
decode(Status, 'LI', 'LI', 'ZZ') from yourtable</FONT>
<BR><FONT COLOR=3D"#0000FF" SIZE=3D2 FACE=3D"Arial">Group by Name, =
decode(Status, 'LI', 'LI', 'ZZ') </FONT>
</P>
<P><FONT COLOR=3D"#0000FF" SIZE=3D2 FACE=3D"Arial">Cheers</FONT>
</P>
<P><B><FONT SIZE=3D2 FACE=3D"Tahoma">Jane Gao</FONT></B>
<BR><B><FONT SIZE=3D2 FACE=3D"Tahoma">Senior Application Support =
Analyst</FONT></B>
<BR><B><FONT SIZE=3D2 FACE=3D"Tahoma">Kalgoorlie Consolidated Gold =
Mines</FONT></B>
<BR><B><FONT SIZE=3D2 =
FACE=3D"Arial">---------------------------------------------------------=
------------------</FONT></B>
<BR><FONT COLOR=3D"#000080" FACE=3D"Wingdings">(</FONT><B></B><B><FONT =
SIZE=3D2 FACE=3D"Arial"> Phone : (08) 9022 1129</FONT></B>
<BR><FONT COLOR=3D"#008000" FACE=3D"Wingdings">.</FONT><B></B><B><FONT =
SIZE=3D2 FACE=3D"Arial">Fax =
: (08) 9022 =
1149</FONT></B>
<BR><FONT COLOR=3D"#FF0000" FACE=3D"Wingdings">+</FONT><B></B><B><FONT =
SIZE=3D2 =
FACE=3D"Arial">Address =
: PMB 27</FONT></B>
<BR><B> =
<FONT SIZE=3D2 =
FACE=3D"Arial"> KALGOORLIE WA =
6430</FONT></B>
<BR><FONT COLOR=3D"#0000FF" FACE=3D"Wingdings">*</FONT><B></B><B><FONT =
SIZE=3D2 FACE=3D"Arial">E-mail : jgao_at_kalgold.com.au </FONT></B>
</P>
<BR>
<UL>
<P><FONT SIZE=3D1 FACE=3D"Arial">-----Original Message-----</FONT>
<BR><B><FONT SIZE=3D1 FACE=3D"Arial">From: </FONT></B> <FONT =
SIZE=3D1 FACE=3D"Arial">Atish Prasad Das =
[SMTP:atish.das_at_wipro.com]</FONT>
<BR><B><FONT SIZE=3D1 FACE=3D"Arial">Sent: </FONT></B> <FONT =
SIZE=3D1 FACE=3D"Arial">Fri, 26 May 2000 18:14</FONT>
<BR><B><FONT SIZE=3D1 =
FACE=3D"Arial">To: </FONT></B> <FONT SIZE=3D1 =
FACE=3D"Arial">Multiple recipients of list ORACLE-L</FONT>
<BR><B><FONT SIZE=3D1 =
FACE=3D"Arial">Subject: </FONT>=
</B> <FONT SIZE=3D1 FACE=3D"Arial">Help regarding query</FONT>
</P>
<P><FONT SIZE=3D2 FACE=3D"Arial">Hi Gurus,</FONT>
<BR><FONT FACE=3D"Arial">=A0</FONT>
<BR><FONT SIZE=3D2 FACE=3D"Arial">Can some one tell me how to do =
this?</FONT>
<BR><FONT FACE=3D"Arial">=A0</FONT>
<BR><FONT SIZE=3D2 FACE=3D"Courier New">Name=A0=A0=A0=A0 Status</FONT>
<BR><FONT SIZE=3D2 FACE=3D"Courier New">----=A0=A0=A0=A0 ------</FONT>
<BR><FONT SIZE=3D2 FACE=3D"Courier New">A=A0=A0=A0=A0=A0=A0=A0=A0 =
LI</FONT>
<BR><FONT SIZE=3D2 FACE=3D"Courier New">A=A0=A0=A0=A0=A0=A0=A0=A0 =
DE</FONT>
<BR><FONT SIZE=3D2 FACE=3D"Courier =
New">A=A0=A0=A0=A0=A0=A0=A0=A0=A0EX</FONT>
<BR><FONT SIZE=3D2 FACE=3D"Courier New">B=A0=A0=A0=A0=A0=A0=A0=A0 =
DE</FONT>
<BR><FONT SIZE=3D2 FACE=3D"Courier New">B=A0=A0=A0=A0=A0=A0=A0=A0 =
EX</FONT>
<BR><FONT SIZE=3D2 FACE=3D"Courier New">C=A0=A0=A0=A0=A0=A0=A0=A0 =
LI</FONT>
<BR><FONT SIZE=3D2 FACE=3D"Courier New">C=A0=A0=A0=A0=A0=A0=A0=A0 =
EX</FONT>
<BR><FONT FACE=3D"Arial">=A0</FONT>
<BR><FONT SIZE=3D2 FACE=3D"Courier New">In the above table, there can =
be only one record per Name where the status will be LI.</FONT>
<BR><FONT SIZE=3D2 FACE=3D"Courier New">The query result should be like =
this:</FONT>
<BR><FONT FACE=3D"Arial">=A0</FONT>
<BR><FONT SIZE=3D2 FACE=3D"Courier New">Result:</FONT>
<BR><FONT FACE=3D"Arial">=A0</FONT>
<BR><FONT SIZE=3D2 FACE=3D"Courier New">Name=A0=A0=A0=A0 Status</FONT>
<BR><FONT SIZE=3D2 FACE=3D"Courier New">----=A0=A0=A0=A0 ------</FONT>
<BR><FONT SIZE=3D2 FACE=3D"Courier =
New">A=A0=A0=A0=A0=A0=A0=A0=A0LI</FONT>
<BR><FONT SIZE=3D2 FACE=3D"Courier New">B=A0=A0=A0=A0=A0=A0=A0 =
ZZ</FONT>
<BR><FONT SIZE=3D2 FACE=3D"Courier New">C=A0=A0=A0=A0=A0=A0=A0 =
LI</FONT>
<BR><FONT FACE=3D"Arial">=A0</FONT>
<BR><FONT SIZE=3D2 FACE=3D"Courier New">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.</FONT></P>
<P><FONT FACE=3D"Arial">=A0</FONT>
<BR><FONT SIZE=3D2 FACE=3D"Courier New">Can somebody help in this =
Received on Sun May 28 2000 - 19:09:33 CDT
