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  |
mmohsinaziz
Messages: 79 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   |
 |
Michel Cadot
Messages: 54127 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
|
|
|
|
|
|
Goto Forum:
Current Time: Sun May 19 02:06:38 CDT 2013
Total time taken to generate the page: 0.12912 seconds
|