Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Query - 2nd Request Pls Help

RE: Query - 2nd Request Pls Help

From: Shockey, David <DShockey_at_jwrinc.com>
Date: Mon, 19 Jun 2000 11:06:02 -0500
Message-Id: <10533.109723@fatcity.com>


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_01BFDA09.ECAF9DDA

Content-Type: text/plain;

        charset="iso-8859-1"

You'll have to give us the logic behind your desired results. As it is, you have lines that have the same cust_id, item_code, and status in the results. So, a simple grouping on these fields is not possible. What other grouping criteria are you using?

-----Original Message-----

From: salu Ullah [mailto:salu_ullah_at_hotmail.com] Sent: Monday, June 19, 2000 10:48 AM
To: Multiple recipients of list ORACLE-L Subject: Query - 2nd Request Pls Help

Hello

I have a table with columns & data as follows

cust_id item_code purc_date status

0001      AAAA      01-JAN-00     A
0001      AAAA      02-JAN-00     A
0001      AAAA      03-JAN-00     A
0001      AAAA      04-JAN-00     B
0001      AAAA      05-JAN-00     B
0002      BBBB      05-JAN-00     A
0001      AAAA      06-JAN-00     A
0002      BBBB      06-JAN-00     A
0001      AAAA      07-JAN-00     A
.          .            .         .
.          .            .         .
0001      AAAA      29-JAN-00     A
0001      AAAA      30-JAN-00     B
0001      AAAA      31-JAN-00     B

I'am looking for a query that will give the folllowing result:

cust_id item_code status from_date to_date

0001      AAAA       A      01-JAN-00   03-JAN-00
0001      AAAA       B      04-JAN-00   05-JAN-00
0002      BBBB       A      05-JAN-00   06-JAN-00
0001      AAAA       A      06-JAN-00   29-JAN-00
0001      AAAA       B      30-JAN-00   31-JAN-00


I tried using min(purc_date) & max(purc_date) & grouping them with the remaining columns,
but was not getting the desired output.

Any input will be appreciated. Thanks in advance

Salu



Get Your Private, Free E-mail from MSN Hotmail at http://www.hotmail.com
-- 
Author: salu Ullah
  INET: salu_ullah_at_hotmail.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists

--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
------_=_NextPart_001_01BFDA09.ECAF9DDA
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.2650.12"> <TITLE>RE: Query - 2nd Request Pls Help</TITLE> </HEAD> <BODY> <P><FONT SIZE=3D2>You'll have to give us the logic behind your desired = results.&nbsp; As it is, you have lines that have the same cust_id, = item_code, and status in the results.&nbsp; So, a simple grouping on = these fields is not possible.&nbsp; What other grouping criteria are = you using?</FONT></P> <P><FONT SIZE=3D2>-----Original Message-----</FONT> <BR><FONT SIZE=3D2>From: salu Ullah [<A = HREF=3D"mailto:salu_ullah_at_hotmail.com">mailto:salu_ullah_at_hotmail.com</A>= ]</FONT> <BR><FONT SIZE=3D2>Sent: Monday, June 19, 2000 10:48 AM</FONT> <BR><FONT SIZE=3D2>To: Multiple recipients of list ORACLE-L</FONT> <BR><FONT SIZE=3D2>Subject: Query - 2nd Request Pls Help</FONT> </P> <BR> <P><FONT SIZE=3D2>Hello</FONT> </P> <P><FONT SIZE=3D2>I have a table with columns &amp; data as = follows</FONT> </P> <P><FONT SIZE=3D2>cust_id&nbsp; item_code&nbsp; purc_date&nbsp; = status</FONT> </P> <P><FONT SIZE=3D2>0001&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; = AAAA&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 01-JAN-00&nbsp;&nbsp;&nbsp;&nbsp; = A</FONT> <BR><FONT SIZE=3D2>0001&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; = AAAA&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 02-JAN-00&nbsp;&nbsp;&nbsp;&nbsp; = A</FONT> <BR><FONT SIZE=3D2>0001&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; = AAAA&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 03-JAN-00&nbsp;&nbsp;&nbsp;&nbsp; = A</FONT> <BR><FONT SIZE=3D2>0001&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; = AAAA&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 04-JAN-00&nbsp;&nbsp;&nbsp;&nbsp; = B</FONT> <BR><FONT SIZE=3D2>0001&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; = AAAA&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 05-JAN-00&nbsp;&nbsp;&nbsp;&nbsp; = B</FONT> <BR><FONT SIZE=3D2>0002&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; = BBBB&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 05-JAN-00&nbsp;&nbsp;&nbsp;&nbsp; = A</FONT> <BR><FONT SIZE=3D2>0001&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; = AAAA&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 06-JAN-00&nbsp;&nbsp;&nbsp;&nbsp; = A</FONT> <BR><FONT SIZE=3D2>0002&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; = BBBB&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 06-JAN-00&nbsp;&nbsp;&nbsp;&nbsp; = A</FONT> <BR><FONT SIZE=3D2>0001&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; = AAAA&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 07-JAN-00&nbsp;&nbsp;&nbsp;&nbsp; = A</FONT> <BR><FONT = SIZE=3D2>.&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; = .&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; = .&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; .</FONT> <BR><FONT = SIZE=3D2>.&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; = .&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; = .&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; .</FONT> <BR><FONT SIZE=3D2>0001&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; = AAAA&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 29-JAN-00&nbsp;&nbsp;&nbsp;&nbsp; = A</FONT> <BR><FONT SIZE=3D2>0001&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; = AAAA&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 30-JAN-00&nbsp;&nbsp;&nbsp;&nbsp; = B</FONT> <BR><FONT SIZE=3D2>0001&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; = AAAA&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 31-JAN-00&nbsp;&nbsp;&nbsp;&nbsp; = B</FONT> </P> <P><FONT SIZE=3D2>I'am looking for a query that will give the = folllowing result:</FONT> </P> <BR> <P><FONT SIZE=3D2>cust_id item_code&nbsp; status&nbsp;&nbsp; = from_date&nbsp;&nbsp;&nbsp; to_date</FONT> <BR><FONT SIZE=3D2>0001&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; = AAAA&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; = A&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 01-JAN-00&nbsp;&nbsp; 03-JAN-00</FONT> <BR><FONT SIZE=3D2>0001&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; = AAAA&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; = B&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 04-JAN-00&nbsp;&nbsp; 05-JAN-00</FONT> <BR><FONT SIZE=3D2>0002&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; = BBBB&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; = A&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 05-JAN-00&nbsp;&nbsp; 06-JAN-00</FONT> <BR><FONT SIZE=3D2>0001&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; = AAAA&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; = A&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 06-JAN-00&nbsp;&nbsp; 29-JAN-00</FONT> <BR><FONT SIZE=3D2>0001&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; = AAAA&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; = B&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 30-JAN-00&nbsp;&nbsp; 31-JAN-00</FONT> </P> <BR> <P><FONT SIZE=3D2>I tried using min(purc_date) &amp; max(purc_date) = &amp; grouping them with the</FONT> <BR><FONT SIZE=3D2>remaining columns,</FONT> <BR><FONT SIZE=3D2>but was not getting the desired output.</FONT> </P> <P><FONT SIZE=3D2>Any input will be appreciated. Thanks in = advance</FONT> </P> <P><FONT SIZE=3D2>Salu</FONT> </P> <P><FONT = SIZE=3D2>_______________________________________________________________= _________</FONT> <BR><FONT SIZE=3D2>Get Your Private, Free E-mail from MSN Hotmail at <A = HREF=3D"http://www.hotmail.com" = TARGET=3D"_blank">http://www.hotmail.com</A></FONT> </P> <P><FONT SIZE=3D2>-- </FONT> <BR><FONT SIZE=3D2>Author: salu Ullah</FONT> <BR><FONT SIZE=3D2>&nbsp; INET: salu_ullah_at_hotmail.com</FONT> </P> <P><FONT SIZE=3D2>Fat City Network Services&nbsp;&nbsp;&nbsp; -- (858) = 538-5051&nbsp; FAX: (858) 538-5051</FONT> <BR><FONT SIZE=3D2>San Diego, = California&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -- Public Internet = access / Mailing Lists</FONT> <BR><FONT = SIZE=3D2>---------------------------------------------------------------=
-----</FONT>
<BR><FONT SIZE=3D2>To REMOVE yourself from this mailing list, send an = E-Mail message</FONT> <BR><FONT SIZE=3D2>to: ListGuru_at_fatcity.com (note EXACT spelling of = 'ListGuru') and in</FONT> <BR><FONT SIZE=3D2>the message BODY, include a line containing: UNSUB = ORACLE-L</FONT> <BR><FONT SIZE=3D2>(or the name of mailing list you want to be removed =
Received on Mon Jun 19 2000 - 11:06:02 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US