1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Hi All. I need to eliminate duplicate lines in this query.

Discussion in 'SQL PL/SQL' started by miuco, Apr 18, 2015.

  1. miuco

    miuco Active Member

    Likes Received:
    Trophy Points:
    SELECT DISTINCT hldrls.release_reason_code, hldef.NAME hold_name, (CASE
    AND hldrls.release_reason_code IS NULL
    THEN 'On credit hold'
    ELSE 'Not on credit hold'
    ) credit_status ,
    wdd.requested_quantity requested_qty,
    msib.unit_weight * wdd.requested_quantity grossweight,
    ((msib.unit_weight * wdd.requested_quantity) / 1000
    ) gross_tons
    FROM oe_order_headers_all ooha,
    oe_order_lines_all oola,
    oe_transaction_types_tl ottt,
    mtl_system_items_b msib,
    mtl_parameters mp,
    hr_all_organization_units ood,
    hz_parties hp,
    hz_party_sites hps,
    hz_locations hl,
    hz_cust_accounts hca,
    wsh_delivery_details wdd,
    oe_order_holds_all hld,
    oe_hold_sources_all hldsrc,
    oe_hold_releases hldrls,
    oe_hold_definitions hldef
    WHERE ooha.header_id = oola.header_id
    AND ottt.transaction_type_id(+) = ooha.order_type_id
    AND ottt.LANGUAGE = 'US'
    AND hca.cust_account_id(+) = ooha.sold_to_org_id
    AND hp.party_id = hca.party_id
    AND ooha.org_id = oola.org_id(+)
    AND ooha.org_id = ood.organization_id
    AND oola.ship_from_org_id = msib.organization_id
    AND msib.inventory_item_id = oola.inventory_item_id
    AND msib.organization_id = mp.organization_id
    AND wdd.source_line_id = oola.line_id
    AND wdd.released_status IN ('B', 'R')
    AND hld.header_id = ooha.header_id(+)
    AND hld.hold_source_id = hldsrc.hold_source_id(+)
    AND hld.hold_source_id = hldrls.hold_source_id(+)
    AND hldsrc.hold_id = hldef.hold_id(+)
    AND hp.party_id = hps.party_id
    AND hps.location_id = hl.location_id
    --AND hldrls.release_reason_code is not null -- added by Joseph to eliminate duplicate lines
    AND ooha.order_number =1511200153--1521201931

    Out put is attached.

    Attached Files:

  2. ocprep

    ocprep Forum Advisor

    Likes Received:
    Trophy Points:
    Orlando, Florida
    The 'lines' aren't duplicated, where a duplicate line is defined as a result row where all columns contain the same value. That's the only type of duplicate that DISTINCT will eliminate. Since that's obviously not the definition you are interested in, ideally you should have provided more information about exactly what you consider a duplicate to be eliminated.

    That said, assuming I understand what you are trying to do -- the core of your problem seems to be in the credit_status column:
    Code (Text):
             WHEN ( hldef.NAME IS NOT NULL AND
                    hldrls.release_reason_code IS NULL)
             THEN 'On credit hold'
             ELSE 'Not on credit hold'
           END credit_status,
    Your duplicate order numbers are generating rows that evaluate to both 'On credit hold' and also to 'Not on credit hold'. I don't know enough about the logic or contents of your tables to make an educated guess about why that would be. Add the hldsrc.hold_source_id and hldsrc.hold_id columns to your query to get the values for this order number. Then query the oe_hold_sources_all, oe_hold_releases, and oe_hold_definitions tables to see why order number 1511200153 is returning both 'On' and 'Not on' credit hold results.