Forum Stats

  • 3,734,283 Users
  • 2,246,938 Discussions
  • 7,857,218 Comments

Discussions

How to Repeat Row by Specific Value from Data Field Itself

Master,

Can You please help me for the query to repeat row by specific value from data field Itself?

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:

Expected Result :

for each line was repeated by pallet_used,

Item : M4010014 repeated by 5 times, and M4010032 repeated by 3 times


Thank You before

Sign In or Register to comment.