Forum Stats

  • 3,769,514 Users
  • 2,252,971 Discussions
  • 7,875,069 Comments

Discussions

UNION

Robeen
Robeen Member Posts: 2,112 Silver Badge

Oracle database 12.1.0.2

AIX 6.4

Hello Team,

I did a UNION between 2 sql statements.

SELECT b.INVENTORY_ITEM_ID,pla.id id2,pha.id id3,max(pha.CURRENCY_CODE)||' ' ||round(max(pla.UNIT_PRICE),2)codeprice,'Last_Issue_Date'
  FROM oracle_financial.po_lines_all pla, oracle_financial.po_headers_all pha,oracle_financial.mtl_system_items_b b
 WHERE NVL (pla.cancel_flag, 'N') = 'N'
  AND pla.po_header_id = pha.po_header_id
  AND pha.approved_flag = 'Y'
  AND pla.item_id = b.INVENTORY_ITEM_ID
  AND pha.po_header_id =
    (SELECT MAX (pla.po_header_id)
    FROM oracle_financial.po_lines_all pla, oracle_financial.po_headers_all pha
    WHERE NVL (pla.cancel_flag, 'N') = 'N'
     AND pla.po_header_id = pha.po_header_id
     AND pha.approved_flag = 'Y'
     AND pla.item_id = b.INVENTORY_ITEM_ID)
 group by b.INVENTORY_ITEM_ID,pla.id ,pha.id 
  
 UNION 
  select b.inventory_item_id inventory_item_id,'id1','id3','codeprice',max(a.transaction_date) Last_Issue_Date
 from oracle_financial.mtl_material_transactions a,oracle_financial.mtl_system_items_b b
 where a.inventory_item_id = b.inventory_item_id
 and transaction_action_id=1
group by b.inventory_item_id

We noticed the output returns 2 records for 1 inventory_item_id instead of 1 record.

incorrect record:

correct record:


Kindly advise how I can eliminate this.


Thank you.

Roshan

Answers

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 41,223 Red Diamond

    Hi, @Robeen

    Whenever you have a question, please post a little sample data (CREATE TABLE and INSERT statements for all tables involved, relevant columns only) so the people who want to help you can re-create the problem and test their ideas. Also post the exact results you want from that data, and explain why you want those results from that data. Always post your complete Oracle version (e.g. 18.4.0.0.0).

    Robeen
  • L. Fernigrini
    L. Fernigrini Data Engineer Sr Consultant Member Posts: 3,669 Silver Crown

    There is no error there.


    UNION removes duplicates if ALL columns have the exact same values. In your example, both rows have different values so UNION keep both of them.

    You can confirm the behaviour with this simple test:


    SELECT 1 AS ID, 'A' AS Label FROM dual
    UNION 
    SELECT 1 AS ID, 'A' AS Label FROM dual;
    --
    SELECT 1 AS ID, 'A' AS Label FROM dual
    UNION 
    SELECT 1 AS ID, 'B' AS Label FROM dual;
    

    Output:


    If you want to "consolidate" both rows into one, you need to decide which value to use from each "source" row and write a query (in the sample you use the date from one row and the code price from other.


    Explain in detail what logic to apply when there are more than 1 rows in order to select the desired value.

    Robeen
  • L. Fernigrini
    L. Fernigrini Data Engineer Sr Consultant Member Posts: 3,669 Silver Crown
    edited Oct 26, 2021 7:45PM

    I have taken a quick view at your code and you need to JOIN between the 2 datasets rather than UNION.

    Try something like this:


    WITH Data1 AS ( 
    SELECT b.INVENTORY_ITEM_ID, pla.id id2, pha.id id3, max(pha.CURRENCY_CODE) ||' ' || round(max(pla.UNIT_PRICE),2) AS codeprice
     FROM oracle_financial.po_lines_all pla, oracle_financial.po_headers_all pha,oracle_financial.mtl_system_items_b b
     WHERE NVL (pla.cancel_flag, 'N') = 'N'
     AND pla.po_header_id = pha.po_header_id
     AND pha.approved_flag = 'Y'
     AND pla.item_id = b.INVENTORY_ITEM_ID
     AND pha.po_header_id =
      (SELECT MAX (pla.po_header_id)
      FROM oracle_financial.po_lines_all pla, oracle_financial.po_headers_all pha
      WHERE NVL (pla.cancel_flag, 'N') = 'N'
       AND pla.po_header_id = pha.po_header_id
       AND pha.approved_flag = 'Y'
       AND pla.item_id = b.INVENTORY_ITEM_ID)
     group by b.INVENTORY_ITEM_ID,pla.id ,pha.id 
    ), 
    Data2 AS (
     select b.inventory_item_id, max(a.transaction_date) Last_Issue_Date
     from oracle_financial.mtl_material_transactions a,oracle_financial.mtl_system_items_b b
     where a.inventory_item_id = b.inventory_item_id
     and transaction_action_id=1
    group by b.inventory_item_id)
    SELECT d1.INVENTORY_ITEM_ID, d1.id2, d1.id3, d1.codeprice, d2.Last_Issue_Date
    FROM Data1 d1
    JOIN Data2 d2 ON d1.INVENTORY_ITEM_ID = d2.INVENTORY_ITEM_ID;
    


    Since you are grouping by 3 columns on the first query and only by 1 on the 2nd one, then you may find that the values for Last Issue Date may be repeated.


    You can also simply add a scalar subquery to the first query or use OUTER APPLY syntax to get the same data.

    Robeen
  • Robeen
    Robeen Member Posts: 2,112 Silver Badge

    Thanks for the update.

    I checked the date against the final results from the 2 datasets we joined.

    I saw for some inventoryid there were null for issue_date and po_price on source database... and these ids were missing in the 2 datasets we joined above. This means there should be a left join of all inventory_id from the main table (mtl_systems_item_b) with the 2 tables we joined above.

    Below is the code I modified.

    SELECT distinct mtl.INVENTORY_ITEM_ID INVENTORY_ITEM_ID,mtl.segment1 ITEM_CODE,price.codeprice LAST_PO_PRICE,isd.Last_Issue_Date

    from oracle_financial.mtl_system_items_b mtl --main table containing all inventory ids

    left join of_cubes.xx_mtg_get_last_poprice2 price on(mtl.inventory_item_id =price.inventory_item_id)

    left join of_cubes.xxmtg_get_max_issuedate isd on (mtl.inventory_item_id =isd.inventory_item_id)

    order by mtl.inventory_item_id