Home » SQL & PL/SQL » SQL & PL/SQL » Replace Reapiting the Same Description with -DO- (Oracle 10G, Windows 2003)
Replace Reapiting the Same Description with -DO- [message #572971] Wed, 19 December 2012 01:37 Go to next message
mmohsinaziz
Messages: 85
Registered: May 2012
Member
Hi Everyone,
I am looking for oracle query to replace repeating description with -DO-.
Sample Data is:
CREATE TABLE SCOTT.ITAX
(
  VDATE        DATE,
  DESCRIPTION  VARCHAR2(50),
  ITAX         NUMBER(2),
  AMOUNT       NUMBER(6)
);

INSERT INTO itax
            (vdate, description, itax, amount
            )
     VALUES (TO_DATE ('01-NOV-12', 'DD-MON-RR'), 'CANOLA SEES', 3, 5600
            );
INSERT INTO itax
            (vdate, description, itax, amount
            )
     VALUES (TO_DATE ('10-NOV-12', 'DD-MON-RR'), 'CANOLA SEES', 3, 5500
            );
INSERT INTO itax
            (vdate, description, itax, amount
            )
     VALUES (TO_DATE ('14-NOV-12', 'DD-MON-RR'), 'CANOLA OIL', 3, 3500
            );
INSERT INTO itax
            (vdate, description, itax, amount
            )
     VALUES (TO_DATE ('25-NOV-12', 'DD-MON-RR'), 'CANOLA OIL', 3, 2500
            );
INSERT INTO itax
            (vdate, description, itax, amount
            )
     VALUES (TO_DATE ('01-DEC-12', 'DD-MON-RR'), 'CANOLA SEES', 3, 5400
            );
INSERT INTO itax
            (vdate, description, itax, amount
            )
     VALUES (TO_DATE ('10-DEC-12', 'DD-MON-RR'), 'CANOLA OIL', 3, 3300
            );

SQL>SELECT * FROM ITAX
order by description,vdate;

VDATE     DESCRIPTION                                              ITAX     AMOUNT
--------- -------------------------------------------------- ---------- ----------
14-NOV-12 CANOLA OIL                                                  3       3500
25-NOV-12 CANOLA OIL                                                  3       2500
10-DEC-12 CANOLA OIL                                                  3       3300
01-NOV-12 CANOLA SEES                                                 3       5600
10-NOV-12 CANOLA SEES                                                 3       5500
01-DEC-12 CANOLA SEES                                                 3       5400

6 rows selected.

Required Output is:

VDATE     DESCRIPTION                                              ITAX     AMOUNT
--------- -------------------------------------------------- ---------- ----------
14-NOV-12 CANOLA OIL                                                  3       3500
25-NOV-12   -DO-                                                      3       2500
10-DEC-12   -DO-                                                      3       3300
01-NOV-12 CANOLA SEES                                                 3       5600
10-NOV-12   -DO-                                                      3       5500
01-DEC-12   -DO-                                                      3       5400


Re: Replace Reapiting the Same Description with -DO- [message #572976 is a reply to message #572971] Wed, 19 December 2012 01:57 Go to previous messageGo to next message
Michel Cadot
Messages: 57604
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> select vdate,
  2         decode(description, 
  3                lag(description) over (partition by description order by vdate), '  -DO-',
  4                description) description,
  5          itax, amount
  6  from itax
  7  order by itax.description, vdate
  8  /
VDATE       DESCRIPTION                                              ITAX     AMOUNT
----------- -------------------------------------------------- ---------- ----------
14-NOV-2012 CANOLA OIL                                                  3       3500
25-NOV-2012   -DO-                                                      3       2500
10-DEC-2012   -DO-                                                      3       3300
01-NOV-2012 CANOLA SEES                                                 3       5600
10-NOV-2012   -DO-                                                      3       5500
01-DEC-2012   -DO-                                                      3       5400

Regards
Michel
Re: Replace Reapiting the Same Description with -DO- [message #572979 is a reply to message #572976] Wed, 19 December 2012 02:43 Go to previous message
mmohsinaziz
Messages: 85
Registered: May 2012
Member
Thanks
Previous Topic: automate inserts from one schema to another
Next Topic: Read CLOB column
Goto Forum:
  


Current Time: Thu Apr 17 03:05:46 CDT 2014

Total time taken to generate the page: 0.13439 seconds