Home » SQL & PL/SQL » SQL & PL/SQL » How to Repeat Row by Specific Value in Data Field (Oracle EBS R12)
How to Repeat Row by Specific Value in Data Field [message #683563] Sun, 31 January 2021 20:44 Go to next message
ags.island
Messages: 3
Registered: January 2021
Junior Member
Master,
Can You help me for the query to repeat row by specific value in data field

Query:
SELECT x.order_number,
       x.ordered_item,
       x.description,
       x.ordered_quantity,
       x.order_quantity_uom,
       x.pallet_used
  FROM (  SELECT ooha.order_number,
                 oola.ordered_item,
                 msib.description,
                 SUM (oola.ordered_quantity) ordered_quantity,
                 oola.order_quantity_uom,
                 msib.global_attribute20 AS pallet_size,
                 CASE
                    WHEN SUM (oola.ordered_quantity) / msib.global_attribute20 <
                            1
                    THEN
                       1
                    ELSE
                       FLOOR (
                          SUM (oola.ordered_quantity) / msib.global_attribute20)
                 END
                    pallet_used
            FROM oe_order_headers_all ooha,
                 oe_order_lines_all oola,
                 mtl_system_items_b msib
           WHERE     ooha.header_id = oola.header_id
                 AND oola.ordered_item = msib.segment1
                 AND oola.ship_from_org_id = msib.organization_id
                 AND ooha.order_number = '112100120'          --p_order_number
        GROUP BY ooha.order_number,
                 oola.ordered_item,
                 msib.description,
                 msib.global_attribute20,
                 oola.order_quantity_uom) x
Output :

ORDER_NUMBER	ORDERED_ITEM	DESCRIPTION			ORDERED_QUANTITY	ORDER_QUANTITY_UOM	PALLET_USED
112100120	M4010014	MBS COCOPANDAN 460 ML X 12	2150			CTN			5
112100120	M4010032	MBS MELON 460 ML X 12		2400			CTN			3
Expected Result :

ORDER_NUMBER	ORDERED_ITEM	DESCRIPTION			ORDERED_QUANTITY	ORDER_QUANTITY_UOM	PALLET_USED
112100120	M4010014	MBS COCOPANDAN 460 ML X 12	2150			CTN			5
112100120	M4010014	MBS COCOPANDAN 460 ML X 12	2150			CTN			5
112100120	M4010014	MBS COCOPANDAN 460 ML X 12	2150			CTN			5
112100120	M4010014	MBS COCOPANDAN 460 ML X 12	2150			CTN			5
112100120	M4010014	MBS COCOPANDAN 460 ML X 12	2150			CTN			5
112100120	M4010032	MBS MELON 460 ML X 12		2400			CTN			3
112100120	M4010032	MBS MELON 460 ML X 12		2400			CTN			3
112100120	M4010032	MBS MELON 460 ML X 12		2400			CTN			3

[Edit MC: add code tags]

[Updated on: Mon, 01 February 2021 00:45] by Moderator

Report message to a moderator

Re: How to Repeat Row by Specific Value in Data Field [message #683565 is a reply to message #683563] Mon, 01 February 2021 00:24 Go to previous messageGo to next message
Michel Cadot
Messages: 67809
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Welcome to the forum.
Please read the OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Indent the code, use code tags and align the columns in result.

Also always post your Oracle version, with 4 decimals (query v$version), as often solution depends on it.


Quote:
How to Repeat Row by Specific Value in Data Field

You have to use a "row generator" like in this example.

Re: How to Repeat Row by Specific Value in Data Field [message #683567 is a reply to message #683565] Mon, 01 February 2021 00:42 Go to previous messageGo to next message
ags.island
Messages: 3
Registered: January 2021
Junior Member
Michel Cadot wrote on Mon, 01 February 2021 00:24

Welcome to the forum.
Please read the OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Indent the code, use code tags and align the columns in result.

Also always post your Oracle version, with 4 decimals (query v$version), as often solution depends on it.


Quote:
How to Repeat Row by Specific Value in Data Field
You have to use a "row generator" like in this example.

Okay, noted
Sorry, I'm new to this.. thank you for the correction

[Updated on: Mon, 01 February 2021 00:47]

Report message to a moderator

Re: How to Repeat Row by Specific Value in Data Field [message #683568 is a reply to message #683567] Mon, 01 February 2021 00:46 Go to previous messageGo to next message
Michel Cadot
Messages: 67809
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

I edited it for you.
(Generally speaking you can edit your post clicking on the "EDIT" button until someone has answered to your post.)

icon14.gif  Re: How to Repeat Row by Specific Value in Data Field [message #683569 is a reply to message #683568] Mon, 01 February 2021 00:50 Go to previous message
ags.island
Messages: 3
Registered: January 2021
Junior Member
Michel Cadot wrote on Mon, 01 February 2021 00:46

I edited it for you.
(Generally speaking you can edit your post clicking on the "EDIT" button until someone has answered to your post.)

I see.. Thanks a lot for editting my post and for the solution that you given was solved my problem

[Updated on: Mon, 01 February 2021 01:31]

Report message to a moderator

Previous Topic: logic sql or plsql (merged)
Next Topic: Transfer the temporary file to the client machine after all files have been processed.
Goto Forum:
  


Current Time: Wed Apr 14 03:20:59 CDT 2021