Home » SQL & PL/SQL » SQL & PL/SQL » Replace Repeating Data with Zero (Oracle 10G, Windows 2003)
| Replace Repeating Data with Zero [message #601116] |
Sat, 16 November 2013 05:28  |
mmohsinaziz
Messages: 110
Registered: May 2012
|
Senior Member |
|
|
Dear Experts,
I am looking for a query that will replace column with zero.
Sample data and required output is below.
DROP TABLE vdata;
CREATE TABLE vdate
(
vdate DATE,
type VARCHAR2(1),
bcode VARCHAR2(2),
scode VARCHAR2(6),
pack NUMBER,
dcat VARCHAR2(1),
tar NUMBER(5,2),
ach NUMBER
)
LOGGING
NOCOMPRESS
NOCACHE
NOPARALLEL
NOMONITORING;
INSERT INTO vdata
(vdate, TYPE, bcode, scode, pack, dcat,
tar, ach
)
VALUES (TO_DATE ('05-nov-13', 'dd-mon-rr'), 'G', '01', '050466', 1, 'A',
4, NULL
);
INSERT INTO vdata
(vdate, TYPE, bcode, scode, pack, dcat,
tar, ach
)
VALUES (TO_DATE ('06-nov-13', 'dd-mon-rr'), 'O', '03', '050466', 2, 'A',
4, .09
);
INSERT INTO vdata
(vdate, TYPE, bcode, scode, pack, dcat,
tar, ach
)
VALUES (TO_DATE ('13-nov-13', 'dd-mon-rr'), 'O', '03', '050466', 2, 'A',
4, .27
);
INSERT INTO vdata
(vdate, TYPE, bcode, scode, pack, dcat,
tar, ach
)
VALUES (TO_DATE ('06-nov-13', 'dd-mon-rr'), 'G', '01', '050466', 3, 'A',
4, 1.75
);
INSERT INTO vdata
(vdate, TYPE, bcode, scode, pack, dcat,
tar, ach
)
VALUES (TO_DATE ('08-nov-13', 'dd-mon-rr'), 'G', '01', '050466', 3, 'A',
4, 1.97
);
INSERT INTO vdata
(vdate, TYPE, bcode, scode, pack, dcat,
tar, ach
)
VALUES (TO_DATE ('12-nov-13', 'dd-mon-rr'), 'G', '01', '050466', 3, 'A',
4, .5
);
INSERT INTO vdata
(vdate, TYPE, bcode, scode, pack, dcat,
tar, ach
)
VALUES (TO_DATE ('13-nov-13', 'dd-mon-rr'), 'G', '01', '050466', 3, 'A',
4, 1.25
);
INSERT INTO vdata
(vdate, TYPE, bcode, scode, pack, dcat,
tar, ach
)
VALUES (TO_DATE ('06-nov-13', 'dd-mon-rr'), 'O', '03', '050466', 4, 'A',
4, .342
);
INSERT INTO vdata
(vdate, TYPE, bcode, scode, pack, dcat,
tar, ach
)
VALUES (TO_DATE ('08-nov-13', 'dd-mon-rr'), 'O', '03', '050466', 4, 'A',
4, .522
);
INSERT INTO vdata
(vdate, TYPE, bcode, scode, pack, dcat,
tar, ach
)
VALUES (TO_DATE ('12-nov-13', 'dd-mon-rr'), 'O', '03', '050466', 4, 'A',
4, .504
);
INSERT INTO vdata
(vdate, TYPE, bcode, scode, pack, dcat,
tar, ach
)
VALUES (TO_DATE ('13-nov-13', 'dd-mon-rr'), 'O', '03', '050466', 4, 'A',
4, .594
);
INSERT INTO vdata
(vdate, TYPE, bcode, scode, pack, dcat,
tar, ach
)
VALUES (TO_DATE ('05-nov-13', 'dd-mon-rr'), 'O', '01', '050466', 5, 'A',
0, null
);
INSERT INTO vdata
(vdate, TYPE, bcode, scode, pack, dcat,
tar, ach
)
VALUES (TO_DATE ('06-nov-13', 'dd-mon-rr'), 'G', '01', '050466', 6, 'A',
45, 2.48
);
INSERT INTO vdata
(vdate, TYPE, bcode, scode, pack, dcat,
tar, ach
)
VALUES (TO_DATE ('08-nov-13', 'dd-mon-rr'), 'G', '01', '050466', 6, 'A',
45, 2.12
);
INSERT INTO vdata
(vdate, TYPE, bcode, scode, pack, dcat,
tar, ach
)
VALUES (TO_DATE ('12-nov-13', 'dd-mon-rr'), 'G', '01', '050466', 6, 'A',
45, 2
);
INSERT INTO vdata
(vdate, TYPE, bcode, scode, pack, dcat,
tar, ach
)
VALUES (TO_DATE ('13-nov-13', 'dd-mon-rr'), 'G', '01', '050466', 6, 'A',
45, 1
);
INSERT INTO vdata
(vdate, TYPE, bcode, scode, pack, dcat,
tar, ach
)
VALUES (TO_DATE ('06-nov-13', 'dd-mon-rr'), 'O', '03', '050466', 7, 'A',
45, 1.8
);
INSERT INTO vdata
(vdate, TYPE, bcode, scode, pack, dcat,
tar, ach
)
VALUES (TO_DATE ('08-nov-13', 'dd-mon-rr'), 'O', '03', '050466', 7, 'A',
45, 1.8
);
INSERT INTO vdata
(vdate, TYPE, bcode, scode, pack, dcat,
tar, ach
)
VALUES (TO_DATE ('12-nov-13', 'dd-mon-rr'), 'O', '03', '050466', 7, 'A',
45, 1.908
);
INSERT INTO vdata
(vdate, TYPE, bcode, scode, pack, dcat,
tar, ach
)
VALUES (TO_DATE ('13-nov-13', 'dd-mon-rr'), 'O', '03', '050466', 7, 'A',
45, 2.7
);
INSERT INTO vdata
(vdate, TYPE, bcode, scode, pack, dcat,
tar, ach
)
VALUES (TO_DATE ('05-nov-13', 'dd-mon-rr'), 'O', '01', '050466', 8, 'A',
0, null
);
INSERT INTO vdata
(vdate, TYPE, bcode, scode, pack, dcat,
tar, ach
)
VALUES (TO_DATE ('05-nov-13', 'dd-mon-rr'), 'M', '01', '050466', 9, 'A',
0, null
);
INSERT INTO vdata
(vdate, TYPE, bcode, scode, pack, dcat,
tar, ach
)
VALUES (TO_DATE ('12-nov-13', 'dd-mon-rr'), 'O', '03', '051967', 2, null,
null, 3.96
);
INSERT INTO vdata
(vdate, TYPE, bcode, scode, pack, dcat,
tar, ach
)
VALUES (TO_DATE ('12-nov-13', 'dd-mon-rr'), 'O', '03', '051967', 4, null,
null, 1.0134
);
INSERT INTO vdata
(vdate, TYPE, bcode, scode, pack, dcat,
tar, ach
)
VALUES (TO_DATE ('12-nov-13', 'dd-mon-rr'), 'O', '03', '051967', 7, null,
null, .7695
);
INSERT INTO vdata
(vdate, TYPE, bcode, scode, pack, dcat,
tar, ach
)
VALUES (TO_DATE ('12-nov-13', 'dd-mon-rr'), 'O', '09', '051967', 7, null,
null, .342
);
Output is :
SQL>SELECT vdate, TYPE, bcode, scode, dcat, ach, pack, tar
FROM vdata
ORDER BY scode, pack, vdate;
VDATE T BC SCODE D ACH PACK TAR
--------- - -- ---------- - ---------- ---------- ----------
05-NOV-13 G 01 050466 A 1 4
06-NOV-13 O 03 050466 A .09 2 4
13-NOV-13 O 03 050466 A .27 2 4
06-NOV-13 G 01 050466 A 1.75 3 4
08-NOV-13 G 01 050466 A 1.97 3 4
12-NOV-13 G 01 050466 A .5 3 4
13-NOV-13 G 01 050466 A 1.25 3 4
06-NOV-13 O 03 050466 A .342 4 4
08-NOV-13 O 03 050466 A .522 4 4
12-NOV-13 O 03 050466 A .504 4 4
13-NOV-13 O 03 050466 A .594 4 4
05-NOV-13 O 01 050466 A 5 0
06-NOV-13 G 01 050466 A 2.48 6 45
08-NOV-13 G 01 050466 A 2.12 6 45
12-NOV-13 G 01 050466 A 2 6 45
13-NOV-13 G 01 050466 A 1 6 45
06-NOV-13 O 03 050466 A 1.8 7 45
08-NOV-13 O 03 050466 A 1.8 7 45
12-NOV-13 O 03 050466 A 1.908 7 45
13-NOV-13 O 03 050466 A 2.7 7 45
05-NOV-13 O 01 050466 A 8 0
05-NOV-13 M 01 050466 A 9 0
12-NOV-13 O 03 051967 3.96 2
12-NOV-13 O 03 051967 1.0134 4
12-NOV-13 O 03 051967 .7695 7
12-NOV-13 O 09 051967 .342 7
Required output is
VDATE T BC SCODE D ACH PACK TAR
--------- - -- ---------- - ---------- ---------- ----------
05-NOV-13 G 01 050466 A 1 4
06-NOV-13 O 03 050466 A .09 2 4
13-NOV-13 O 03 050466 A .27 2 0
06-NOV-13 G 01 050466 A 1.75 3 4
08-NOV-13 G 01 050466 A 1.97 3 0
12-NOV-13 G 01 050466 A .5 3 0
13-NOV-13 G 01 050466 A 1.25 3 0
06-NOV-13 O 03 050466 A .342 4 4
08-NOV-13 O 03 050466 A .522 4 0
12-NOV-13 O 03 050466 A .504 4 0
13-NOV-13 O 03 050466 A .594 4 0
05-NOV-13 O 01 050466 A 5 0
06-NOV-13 G 01 050466 A 2.48 6 45
08-NOV-13 G 01 050466 A 2.12 6 0
12-NOV-13 G 01 050466 A 2 6 0
13-NOV-13 G 01 050466 A 1 6 0
06-NOV-13 O 03 050466 A 1.8 7 45
08-NOV-13 O 03 050466 A 1.8 7 0
12-NOV-13 O 03 050466 A 1.908 7 0
13-NOV-13 O 03 050466 A 2.7 7 0
05-NOV-13 O 01 050466 A 8 0
05-NOV-13 M 01 050466 A 9 0
12-NOV-13 O 03 051967 3.96 2 0
12-NOV-13 O 03 051967 1.0134 4 0
12-NOV-13 O 03 051967 .7695 7 0
12-NOV-13 O 09 051967 .342 7 0
TAR columns is repeating with PACK column data. I need your help to write a query to replace the repeating data with zero.
|
|
|
|
|
|
| Re: Replace Repeating Data with Zero [message #601371 is a reply to message #601117] |
Wed, 20 November 2013 04:11  |
mmohsinaziz
Messages: 110
Registered: May 2012
|
Senior Member |
|
|
Dear Mr. Solomon,
Sorry for the late reply.
Thanks for your reply. As per your advice, here is the query. Please let us know if there is other way to get the result.
SQL> SELECT vdate, TYPE, bcode, scode, dcat, pack, ach, tar,
NVL
(CASE
WHEN ROW_NUMBER () OVER (PARTITION BY TRUNC (vdate, 'mm'), scode, pack ORDER BY scode,
pack, vdate) = 1
THEN tar
END,
0
) tarrn
FROM vdata
ORDER BY scode, pack, vdate;
VDATE TYPE BC SCODE D PACK ACH TAR TARRN
--------- ---------------------------------------- -- ---------- - ---------- ---------- ---------- ----------
05-NOV-13 G 01 050466 A 1 4 4
06-NOV-13 O 03 050466 A 2 .09 4 4
13-NOV-13 O 03 050466 A 2 .27 4 0
06-NOV-13 G 01 050466 A 3 1.75 4 4
08-NOV-13 G 01 050466 A 3 1.97 4 0
12-NOV-13 G 01 050466 A 3 .5 4 0
13-NOV-13 G 01 050466 A 3 1.25 4 0
06-NOV-13 O 03 050466 A 4 .342 4 4
08-NOV-13 O 03 050466 A 4 .522 4 0
12-NOV-13 O 03 050466 A 4 .504 4 0
13-NOV-13 O 03 050466 A 4 .594 4 0
05-NOV-13 O 01 050466 A 5 0 0
06-NOV-13 G 01 050466 A 6 2.48 45 45
08-NOV-13 G 01 050466 A 6 2.12 45 0
12-NOV-13 G 01 050466 A 6 2 45 0
13-NOV-13 G 01 050466 A 6 1 45 0
06-NOV-13 O 03 050466 A 7 1.8 45 45
08-NOV-13 O 03 050466 A 7 1.8 45 0
12-NOV-13 O 03 050466 A 7 1.908 45 0
13-NOV-13 O 03 050466 A 7 2.7 45 0
05-NOV-13 O 01 050466 A 8 0 0
05-NOV-13 M 01 050466 A 9 0 0
12-NOV-13 O 03 051967 2 3.96 0
12-NOV-13 O 03 051967 4 1.0134 0
12-NOV-13 O 03 051967 7 .7695 0
12-NOV-13 O 09 051967 7 .342 0
Once again thanks for your support.
|
|
|
|
|
|
Goto Forum:
Current Time: Thu May 09 10:04:41 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
