RE: Query - 2nd Request Pls Help

From: Toepke, Kevin M <ktoepke_at_cms.cendant.com>
Date: Mon, 19 Jun 2000 12:30:38 -0400
Message-Id: <10533.109725@fatcity.com>

Salu:

I believe the only way to accomplish this is via PL/SQL or other 3GL.

I think the following block of code will give you the desired results. I haven't tested it.

Kevin

DECLARE
    CURSOR c1 IS

        SELECT cust_id, item_code, status, purc_date
        FROM   my_table
        ORDER BY purc_date;
    ld_from_date         my_table.purc_date%TYPE;
    ld_ld_date           my_table.purc_date%TYPE;
    lr_old_rec           c1%ROWTYPE;

BEGIN
    FOR r1 IN c1 LOOP

        IF (lr_old_rec.cust_id IS NOT NULL) THEN
            IF ((r1.cust_id     != lr_old_rec.cust_id) OR
                (r1.item_code   != lr_old_rec.item_code) OR
                (r1.status      != lr_old_rec.status)) THEN
               DBMS_OUTPUT.PUT_LINE(lr_old_rec.cust_id || '/' ||
                                    lr_old_rec.item_code || '/' ||
                                    lr_old_rec.status    || '/' ||
                                    ld_from_date         || '/' ||
                                    ld_to_date);
                lr_old_rec.cust_id     := r1.cust_id;
                lr_old_rec.item_code   := r1.item_code;
                lr_old_rec.status      := r1.status;
            ELSE
                ld_to_date     := r1.purc_date;
            END IF;
        END IF;

    END LOOP;

    DBMS_OUTPUT.PUT_LINE(lr_old_rec.cust_id || '/' ||
                         lr_old_rec.item_code || '/' ||
                         lr_old_rec.status    || '/' ||
                         ld_from_date         || '/' ||
                         ld_to_date);

END;
/

+-----------------------------------------------------------+


| ktoepke_at_cms.cendant.com                                   |

| Phone: 614/652-5117                                       |

| Fax  : 614/652-5401                                       |

+-----------------------------------------------------------+

-----Original Message-----
From: salu Ullah [mailto:salu_ullah_at_hotmail.com] Sent: Monday, June 19, 2000 11: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

Received on Mon Jun 19 2000 - 11:30:38 CDT