Help in Query for Required output [message #667018] |
Tue, 05 December 2017 05:18 |
|
shawaj
Messages: 89 Registered: January 2016
|
Member |
|
|
Hi,
Please help me for required output.
create table DT_BAR_HIST
(
PO VARCHAR2(8),
STYLENO NUMBER(6),
ITEM_CODE NUMBER(3),
QTY NUMBER(12),
CH_Y_N CHAR(1),
DT_MODIFY DATE default SYSDATE
)
Current output
SQL> SELECT PO,STYLENO,ITEM_CODE,QTY FROM DT_BAR_HIST;
PO STYLENO ITEM_CODE QTY
-------- ------- --------- -------------
S1796 82871 15 5
S1797 82872 16 3
Required output
PO STYLENO ITEM_CODE QTY
-------- ------- --------- -------------
S1796 82871 15 5
S1796 82871 15 5
S1796 82871 15 5
S1796 82871 15 5
S1796 82871 15 5
S1797 82872 16 3
S1797 82872 16 3
S1797 82872 16 3
S1797 82872 16 3
|
|
|
|
|
Re: Help in Query for Required output [message #667025 is a reply to message #667024] |
Tue, 05 December 2017 07:07 |
|
shawaj
Messages: 89 Registered: January 2016
|
Member |
|
|
insert ..
insert into DT_BAR_HIST (PO, STYLENO, ITEM_CODE, QTY, CH_Y_N, DT_MODIFY)
values ('S1796', 82871, 15, 5, 'Y', to_date('05-12-2017 16:05:03', 'dd-mm-yyyy hh24:mi:ss'));
insert into DT_BAR_HIST (PO, STYLENO, ITEM_CODE, QTY, CH_Y_N, DT_MODIFY)
values ('S1797', 82872, 16, 3, 'Y', to_date('05-12-2017 16:05:03', 'dd-mm-yyyy hh24:mi:ss'));
"Please explain the logic by which the desired output is derived from the (as yet unknown) input."
no of rows depends on Quantity, for example for qty= 5 then record should be repeat 5 times or qty=3 record should be repeat 3 times.
|
|
|
|
|
|
Re: Help in Query for Required output [message #667035 is a reply to message #667018] |
Tue, 05 December 2017 09:54 |
|
JPBoileau
Messages: 88 Registered: September 2017
|
Member |
|
|
Probably not the the best of the best solution but this would give you the right output.
SELECT PO,STYLENO,ITEM_CODE,QTY FROM (
SELECT DISTINCT PO,STYLENO,ITEM_CODE,QTY , LEVEL
FROM DT_BAR_HIST
CONNECT BY LEVEL <= QTY)
ORDER BY PO,STYLENO,ITEM_CODE,QTY
PO STYLENO ITEM_CODE QTY
-------- ---------- ---------- ----------
S1796 82871 15 5
S1796 82871 15 5
S1796 82871 15 5
S1796 82871 15 5
S1796 82871 15 5
S1797 82872 16 3
S1797 82872 16 3
S1797 82872 16 3
JP
|
|
|
|
|
|
|
|
|
|
|
|
|