How to avoid duplicate records of primary key due to one of the fields having 2 different values for same surrogate key value & it is extracted twice?

Question

I have a primary key called ORDER_LINE_ID. This primary key is present in ORDER_LINE(OL) table and ORDER_RELEASE_STATUS (ORS) table. Now, I want to extract a field called STATUS_TS from ORS table. So I create a join and extarct the fields to a consolidated table called CACHED_ORDER_LINE_RELEASE (COLR) as shown below. A snippet of the code is given below for reference:

Select

  COLR.ORDER_DT ORDER_DT,
  COLR.ORDER_LINE_ID ORDER_LINE_ID,
  COLR.ORDER_HEADER_KEY ORDER_HEADER_KEY,
  COLR.ORDER_LINE_KEY ORDER_LINE_KEY,
  MIN(CASE WHEN COLR.STATUS_QTY > 0 THEN COLR.STATUS_ID END)  LINE_STATUS,
  --Max(CASE WHEN COLR.STATUS_QTY > 0 THEN COLR.STATUS_QTY END) UNIT,

  SUBSTRING(MIN(CONCAT(LPAD( CASE WHEN COLR.STATUS_QTY > 0 THEN COLR.STATUS_ID END , 11, '0'), COLR.STATUS_QTY)), 12) AS UNIT,

  SUBSTRING(MIN(CONCAT(  LPAD( CASE WHEN COLR.STATUS_QTY > 0 THEN COLR.STATUS_ID END , 11, '0'), COLR.STATUS_DESC)), 12) AS LINE_DESCRIPTION,

  COLR.ITEM_KEY ITEM_KEY,
  COLR.PRODUCT_LINE PRODUCT_LINE,
  COLR.SHIP_NODE_CD SHIP_NODE_CD,
  COLR.RECEIVING_NODE_CD RECEIVING_NODE_CD,
  COLR.LINE_TOTAL_AMT LINE_TOTAL_AMT,                                   --Nile bpk
  COLR.ADDITIONAL_LINE_TYPE_CD ADDITIONAL_LINE_TYPE_CD,                 --Nile bpk
  COLR.RETURN_ACTION_CD RETURN_ACTION_CD,                               --Nile bpk
  COLR.ORDER_QTY ORDER_QTY,                                             --Nile bpk
  COLR.RETURN_REASON_CD RETURN_REASON_CD,                               --Nile bpk
  COLR.RETURN_SUB_REASON_CD RETURN_SUB_REASON_CD,                       --Nile bpk
  COLR.RETURN_REASON_DESC RETURN_REASON_DESC,                           --Nile bpk
  COLR.RETURN_ACTION RETURN_ACTION,                                     --Nile bpk
  MIN(COLR.STATUS_TS) STATUS_TS,                                        --Nile bpk

  MIN(CASE WHEN  (TRIM(COLR.STATUS_ID)) ='3700' THEN (COLR.INSERT_TS) ELSE NULL END) ORDER_SHIPPED_DT,
  MIN(CASE WHEN  (TRIM(COLR.STATUS_ID)) ='3700.8000' THEN (COLR.INSERT_TS) ELSE NULL END) STORE_RECEIVED_DT,
  MIN(CASE WHEN  (TRIM(COLR.STATUS_ID)) ='3700.9000' THEN (COLR.INSERT_TS) ELSE NULL END) CUSTOMER_PICKED_UP_DATE,
  MIN(CASE WHEN  (TRIM(COLR.STATUS_ID)) in ('9000','3700.01.545','3200.525','9000.300','3200.520','3700.01.540','1100.525') OR (TRIM(COLR.STATUS_ID)) >= '9000'  THEN (COLR.INSERT_TS) ELSE NULL END) CANCELLED_DATE,
  
  MIN(CASE WHEN  (TRIM(COLR.STATUS_ID)) ='3700.9000' THEN (COLR.INSERT_TS) ELSE NULL END) CUSTOMER_PICKUP_TS,            --Nile bpk
  MIN(CASE WHEN  (TRIM(COLR.STATUS_ID)) ='1500.102' THEN (COLR.INSERT_TS) ELSE NULL END) ORDER_DROP_TS,                  --Nile bpk
  
  MIN(CASE WHEN  (TRIM(COLR.STATUS_ID)) in ('3700.01','3700.01.01','3700.02') THEN (COLR.INSERT_TS) ELSE NULL END) RETURN_DATE,

  MIN(CASE WHEN  (TRIM(COLR.STATUS_ID)) in ('1100','1100.200','1100.525','1300','1310') AND COLR.STATUS_QTY > 0 THEN (COLR.STATUS_QTY) ELSE NULL END) PENDING_OMS_QTY,

  MIN(CASE WHEN  (TRIM(COLR.STATUS_ID)) in ('1500','1500.100','1500.101','3200','3200.050','3200.100','3200.200','3200.500','3200.520') AND COLR.STATUS_QTY > 0 THEN (COLR.STATUS_QTY) ELSE NULL END) PENDING_SHIPMENT_QTY,
  MIN(CASE WHEN  (TRIM(COLR.STATUS_ID)) in ('3700','3700.00.03','3700.01.03','3700.01.540','3700.500','3700.7777') AND COLR.STATUS_QTY > 0 THEN (COLR.STATUS_QTY) ELSE NULL END) IN_TRANSIT_QTY,

  MIN(CASE WHEN  (TRIM(COLR.STATUS_ID)) ='3700.8000' AND COLR.STATUS_QTY > 0 THEN (COLR.STATUS_QTY) ELSE NULL END) AWAITING_PICKUP_QTY,
  MIN(CASE WHEN  (TRIM(COLR.STATUS_ID)) ='3700.9000' AND COLR.STATUS_QTY > 0 THEN (COLR.STATUS_QTY) ELSE NULL END) CUSTOMER_PICKED_UP_QTY,
  MIN(CASE WHEN  (TRIM(COLR.STATUS_ID)) in ('1300','1310','1500','1500.100') AND COLR.STATUS_QTY > 0 THEN (COLR.STATUS_QTY) ELSE NULL END) BO_STATUS_QTY --Added for BOSTS story by NILE team

FROM
    (select
      OL.ORDER_DT ORDER_DT,
      trim(OL.ORDER_LINE_ID) ORDER_LINE_ID,
      OL.ORDER_HEADER_KEY ORDER_HEADER_KEY,
      OL.ORDER_LINE_KEY ORDER_LINE_KEY,
      OL.ITEM_KEY ITEM_KEY,
      trim(OL.PRODUCT_LINE) PRODUCT_LINE,
      trim(OL.SHIP_NODE_CD) SHIP_NODE_CD,
      trim(OL.RECEIVING_NODE_CD) RECEIVING_NODE_CD,
      OL.LINE_TOTAL_AMT LINE_TOTAL_AMT,                                              --Nile bpk
      trim(OL.ADDITIONAL_LINE_TYPE_CD) ADDITIONAL_LINE_TYPE_CD,                      --Nile bpk
      trim(OL.RETURN_ACTION_CD) RETURN_ACTION_CD,                                    --Nile bpk
      OL.ORDER_QTY ORDER_QTY,                                                        --Nile bpk
      trim(OL.RETURN_REASON_CD) RETURN_REASON_CD,                                    --Nile bpk
      trim(OL.RETURN_SUB_REASON_CD) RETURN_SUB_REASON_CD,                            --Nile bpk
      trim(OL.RETURN_REASON_DESC) RETURN_REASON_DESC,                                --Nile bpk
      trim(OL.RETURN_ACTION) RETURN_ACTION,                                          --Nile bpk
      trim(ORS.STATUS_ID) STATUS_ID,
      ORS.STATUS_QTY STATUS_QTY,
      ORS.STATUS_TS STATUS_TS,                                                       --Nile bpk
      ORS.INSERT_TS INSERT_TS,                                                       --Nie bpk
      trim(ORS.STATUS_DESC) STATUS_DESC
    from
      (SELECT ORDER_DT ORDER_DT,
                trim(ORDER_LINE_ID) ORDER_LINE_ID,
                ORDER_HEADER_KEY ORDER_HEADER_KEY,
                ORDER_LINE_KEY ORDER_LINE_KEY,
                ITEM_KEY ITEM_KEY,
                trim(PRODUCT_LINE) PRODUCT_LINE,
                trim(SHIP_NODE_CD) SHIP_NODE_CD,
                trim(RECEIVING_NODE_CD) RECEIVING_NODE_CD, --- Duplicate Store
                LINE_TOTAL_AMT LINE_TOTAL_AMT,                                               --Nile bpk
                trim(ADDITIONAL_LINE_TYPE_CD) ADDITIONAL_LINE_TYPE_CD,                       --Nile bpk
                trim(RETURN_ACTION_CD) RETURN_ACTION_CD,                                     --Nile bpk
                ORDER_QTY ORDER_QTY,                                                         --Nile bpk
                trim(RETURN_REASON_CD) RETURN_REASON_CD,                                     --Nile bpk
                trim(RETURN_SUB_REASON_CD) RETURN_SUB_REASON_CD,                             --Nile bpk
                trim(RETURN_REASON_DESC) RETURN_REASON_DESC,                                 --Nile bpk
                trim(RETURN_ACTION) RETURN_ACTION                                            --Nile bpk
             FROM $L2_ANALYTICS.ORDER_LINE
             where
                trim(CARRIER_SERVICE_CD) = 'PICKUP_INTERNAL'
                and ORDER_LINE_TYPE != 'GCARD'
                and ( trim(KIT_CD) is null or trim(KIT_CD) = '' )
                and trim(PRODUCT_LINE) is not null
                and SOURCE_SYSTEM = 'STERLING_DTC'
                and ORDER_DT >= '$FROM_DATE'


      ) OL
      left join (


             SELECT trim(STATUS_ID) STATUS_ID,
                    STATUS_QTY STATUS_QTY,
                    STATUS_TS STATUS_TS,          --Nile bpk
                    INSERT_TS INSERT_TS,          --Nile bpk
                    trim(STATUS_DESC) STATUS_DESC,
                    trim(ORDER_LINE_ID) ORDER_LINE_ID,
                    ORDER_DT
              FROM $L2_STAGE.ORDER_RELEASE_STATUS
              WHERE  trim(DOCUMENT_TYPE) = '0001'
              and trim(STATUS_ID) > '1000'
              and ORDER_DT >= '$FROM_DATE'


      ) ORS

         on trim(OL.ORDER_LINE_ID) = trim(ORS.ORDER_LINE_ID)
         and ORS.ORDER_DT = OL.ORDER_DT

      ) COLR

GROUP BY
 COLR.ORDER_DT,
 COLR.ORDER_LINE_ID,
 COLR.ORDER_LINE_KEY,
 COLR.PRODUCT_LINE,
 COLR.ITEM_KEY,
 COLR.ORDER_HEADER_KEY,
 COLR.SHIP_NODE_CD,
 COLR.RECEIVING_NODE_CD,
 COLR.LINE_TOTAL_AMT,                                         --Nile bpk
 COLR.ADDITIONAL_LINE_TYPE_CD,                                --Nile bpk
 COLR.RETURN_ACTION_CD,                                       --Nile bpk
 COLR.ORDER_QTY,                                              --Nile bpk
 COLR.RETURN_REASON_CD,                                       --Nile bpk
 COLR.RETURN_SUB_REASON_CD,                                   --Nile bpk
 COLR.RETURN_REASON_DESC,                                     --Nile bpk
 COLR.RETURN_ACTION,                                           --Nile bpk
 COLR.STATUS_TS                                               --Nile bpk

HAVING
LINE_STATUS not in ('9000','3700.01.545','3200.525','9000.300','3200.520','3700.01.540','1100.525')  --Cancelled
AND LINE_STATUS < '9000' -- Cancelled
AND LINE_STATUS NOT IN ('3700.01','3700.01.01','3700.02') --- Returned
 --AND RETURN_DATE IS NULL

As I said, in the consolidated table, the ORDER_LINE_ID value should not be repeated. There must be only 1 record for one value of ORDER_LINE_ID. But somehow, COLR table gets 2 records with same ORDER_LINE_ID value but different STATUS_TS values despite using the MIN(COLR.STATUS_TS) condition. Can someone point out where I am going wrong here?

Sample Output showing the problem:

Would appreciate any insights. Thanks!

Also, it's better to eliminate the duplicates from `ORDER_RELEASE_STATUS` before performing join with `ORDER_LINE` to reduce the rows to be joined. — blackbishop, Jan 30 '22 at 12:28
@blackbishop Do you mean to say that removing COLR.STATUS_TS will solve it? To remove duplicates from ORS table, should I use MIN(STATUS_TS) condition? I am wondering where to use it. Is it in the ORS table select query or the query outside of it, that extracts the joined fields from OL & ORS? Like MIN(ORS.STATUS_TS)? — BetaNumero2222, Jan 30 '22 at 16:40
Removing `COLR.STATUS_TS` from the groupy by clause should work. To remove duplicates, you can use `row_number` in subquery where you select from `$L2_STAGE.ORDER_RELEASE_STATUS` (see [example](https://stackoverflow.com/a/50148723/1386551)) — blackbishop, Jan 30 '22 at 20:12

score 0 · Answer 1 · answered Feb 02 '22 at 01:25

I ended adding a self join to ORDER_RELEASE_STATUS table as shown below. This helped.

         SELECT trim(a.STATUS_ID) STATUS_ID,
                a.STATUS_QTY STATUS_QTY,
                a.STATUS_TS STATUS_TS,          --Nile bpk
                a.INSERT_TS INSERT_TS,          --Nile bpk
                trim(a.STATUS_DESC) STATUS_DESC,
                trim(a.ORDER_LINE_ID) ORDER_LINE_ID,
                a.ORDER_DT
          FROM $L2_STAGE.ORDER_RELEASE_STATUS a
          
          left join $L2_STAGE.ORDER_RELEASE_STATUS b
          on a.ORDER_LINE_ID = b.ORDER_LINE_ID
         -- and a.ORDER_DT =b.ORDER_DT
          
          WHERE  trim(a.DOCUMENT_TYPE) = '0001'
          and trim(a.STATUS_ID) > '1000'
          and a.ORDER_DT >= '$FROM_DATE'
          and a.STATUS_TS = b.STATUS_TS
          


  ) ORS

How to avoid duplicate records of primary key due to one of the fields having 2 different values for same surrogate key value & it is extracted twice?

1 Answers1