Home » SQL & PL/SQL » SQL & PL/SQL » Help in Query for Required output (Oracle8i Enterprise Edition Release 8.1.7.0.0)
Help in Query for Required output [message #667018] Tue, 05 December 2017 05:18 Go to next message
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 #667021 is a reply to message #667018] Tue, 05 December 2017 06:15 Go to previous messageGo to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Are you sure that this is the required output?
Re: Help in Query for Required output [message #667024 is a reply to message #667018] Tue, 05 December 2017 06:31 Go to previous messageGo to next message
EdStevens
Messages: 1376
Registered: September 2013
Senior Member
Please provide INSERT statements to create representative test data.
Please explain the logic by which the desired output is derived from the (as yet unknown) input.
Re: Help in Query for Required output [message #667025 is a reply to message #667024] Tue, 05 December 2017 07:07 Go to previous messageGo to next message
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 #667027 is a reply to message #667025] Tue, 05 December 2017 07:53 Go to previous messageGo to next message
joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA
Senior Member
Than why are there 4 rows for S1797?
Re: Help in Query for Required output [message #667029 is a reply to message #667027] Tue, 05 December 2017 07:57 Go to previous messageGo to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Our (Ed's & mine) question, exactly.

[Updated on: Tue, 05 December 2017 07:57]

Report message to a moderator

Re: Help in Query for Required output [message #667031 is a reply to message #667029] Tue, 05 December 2017 08:34 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
I think we can safely assume that's a case of one paste too many at this point.
Re: Help in Query for Required output [message #667035 is a reply to message #667018] Tue, 05 December 2017 09:54 Go to previous messageGo to next message
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
Re: Help in Query for Required output [message #667041 is a reply to message #667025] Tue, 05 December 2017 14:07 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

http://www.orafaq.com/forum/mv/msg/95011/493455/#msg_493455

Re: Help in Query for Required output [message #667046 is a reply to message #667035] Tue, 05 December 2017 23:03 Go to previous messageGo to next message
shawaj
Messages: 89
Registered: January 2016
Member
Thanks a lot..
but i have mention that my oracle version is "Oracle8i Enterprise Edition Release 8.1.7.0.0" so this not solution.
Re: Help in Query for Required output [message #667047 is a reply to message #667027] Tue, 05 December 2017 23:03 Go to previous messageGo to next message
shawaj
Messages: 89
Registered: January 2016
Member
joy_division wrote on Tue, 05 December 2017 07:53
Than why are there 4 rows for S1797?
sorry man
it will be only 3 times not 4

[Updated on: Tue, 05 December 2017 23:04]

Report message to a moderator

Re: Help in Query for Required output [message #667049 is a reply to message #667046] Wed, 06 December 2017 01:32 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

And if you did read the link I provided you'd find a solution.

Re: Help in Query for Required output [message #667054 is a reply to message #667049] Wed, 06 December 2017 02:59 Go to previous messageGo to next message
shawaj
Messages: 89
Registered: January 2016
Member
Michel Cadot wrote on Wed, 06 December 2017 01:32

And if you did read the link I provided you'd find a solution.

i am getting this error
SQL> select level line from dual connect by level<=3;
 
select level line from dual connect by level<=3
 
ORA-01436: CONNECT BY loop in user data
Re: Help in Query for Required output [message #667058 is a reply to message #667054] Wed, 06 December 2017 03:28 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

This is expected in your version, you have to read the topic, it gives the way to do it for all versions (even before yours).

Re: Help in Query for Required output [message #667074 is a reply to message #667058] Wed, 06 December 2017 06:43 Go to previous messageGo to next message
shawaj
Messages: 89
Registered: January 2016
Member
Thanks to all.
Re: Help in Query for Required output [message #667077 is a reply to message #667074] Wed, 06 December 2017 07:56 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

So how did you do it in the end?

Re: Help in Query for Required output [message #667080 is a reply to message #667046] Wed, 06 December 2017 08:12 Go to previous messageGo to next message
EdStevens
Messages: 1376
Registered: September 2013
Senior Member
shawaj wrote on Tue, 05 December 2017 23:03
Thanks a lot..
but i have mention that my oracle version is "Oracle8i Enterprise Edition Release 8.1.7.0.0" so this not solution.
Please accept my sympathy for having to use a database version that went out of support in the last millennium. If the rest of your technology stack is equally old, then you cannot even get replacement parts for your hardware.
Re: Help in Query for Required output [message #667084 is a reply to message #667077] Wed, 06 December 2017 09:30 Go to previous message
shawaj
Messages: 89
Registered: January 2016
Member
using the following ...
SQL> SELECT ROWNUM 
  2  FROM ALL_OBJECTS
  3  WHERE ROWNUM <= &N
  4  /
    ROWNUM
----------
         1
         2
         3
Previous Topic: Number Conversion
Next Topic: How to convert column to rows
Goto Forum:
  


Current Time: Fri Mar 29 03:04:01 CDT 2024