SQL QUERY DUPLICATES from Join Error?

amartin1 — Wed, 08 Mar 2023 20:00:38 GMT

Please help to clarify what record / join is causing the thousands of duplicates. Kindly note, I have no formal training in SQL and appreciate simplified explanation or directions of how to correct. Thank you in advance!

Goal is to create a date or date range query which identifies Sales Department Closed/Voided Orders associated with Finance Department Open Items (item represents money)

Below is the SQL statement

Query SQL

SELECT TO_CHAR(A.BOOKING_DT_FZ,'YYYY-MM-DD'), A.PREPARED_BY_FZ, C.ORDERED_FROM, A.CUST_ID, A.ORDER_REF_FZ, B.EQUIP_FZ, A.DEPOT_CD_FZ, B.BOOKING_QTY_FZ, B.PICKUP_QTY_FZ, ( B.BOOKING_QTY_FZ)-( B.PICKUP_QTY_FZ), TO_CHAR(CAST((A.FIRST_SEND_DT_FZ) AS TIMESTAMP),'YYYY-MM-DD-HH24.MI.SS.FF'), TO_DATE(TO_CHAR(SYSDATE,'YYYY-MM-DD'),'YYYY-MM-DD') - TO_DATE( TO_CHAR(A.BOOKING_DT_FZ,'YYYY-MM-DD'),'YYYY-MM-DD'), C.BOOKING_PRICE, C.TAX_AMT, C.DISCOUNT_AMT, C.DISCOUNTED_PRICE, C.DISCOUNT_TYPE_FZ, C.USED_REWARD_POINT, C.RESALE_COUPON_ID, C.PAYMENT_METHOD, A.ORDER_STATUS_FZ, D.ITEM, D.ITEM_STATUS, D.BAL_AMT
FROM (((PS_FZ_BOOKING A INNER JOIN PS_FZ_SP_BU_OPSCLS A1 ON (A.BUSINESS_UNIT = A1.BUSINESS_UNIT AND A1.OPRCLASS = 'HKBUS' )) LEFT OUTER JOIN PS_FZ_W_RS_ORDER C ON A.ORDER_REF_FZ = C.ORDER_REF_FZ ) LEFT OUTER JOIN PS_ITEM_DSP_VW D ON D.CUST_ID = C.CUST_ID ), PS_FZ_BOOK_EQUIP B, PS_FZ_SP_BU_OPSCLS B1
WHERE ( B.BUSINESS_UNIT = B1.BUSINESS_UNIT
    AND B1.OPRCLASS = 'HKBUS'
    AND ( A.MOVE_TYPE_FZ = 'RSL'
     AND A.ORDER_STATUS_FZ IN ('C','O','V')
     AND A.BUSINESS_UNIT = B.BUSINESS_UNIT
     AND A.PREPARED_BY_FZ IN ('LHALL','PAGRILLO','FGABRIELE','CRISTIAN','VINCCODI','RYANCHOI','ATAMBINI')
     AND A.CUST_ID = :3
     AND A.ORDER_REF_FZ = B.ORDER_REF_FZ
     AND B.BOOKING_QTY_FZ <> B.PICKUP_QTY_FZ
     AND A.BOOKING_DT_FZ BETWEEN TO_DATE(:1,'YYYY-MM-DD') AND TO_DATE(:2,'YYYY-MM-DD')
     AND D.EFFDT =
        (SELECT MAX(D_ED.EFFDT) FROM PS_ITEM_DSP_VW D_ED
        WHERE D.SETID = D_ED.SETID
          AND D.BUSINESS_UNIT = D_ED.BUSINESS_UNIT
          AND D.CUST_ID = D_ED.CUST_ID
          AND D.ITEM = D_ED.ITEM
          AND D.ITEM_LINE = D_ED.ITEM_LINE
          AND D_ED.EFFDT <= SYSDATE) ))

Re: SQL QUERY DUPLICATES from Join Error?

ballardw — Wed, 08 Mar 2023 22:04:34 GMT

Not an error in way shape or form, it is a designed and useful feature of SQL. Any of the JOINs with duplicate values of ON variables will by default create a combination of the two records.

A small example that you can run to see what what happens IF you are using SAS. The small number of records, variables and unique values below make it pretty easy to see that where X is duplicated in both set One and Two that the Join matches all the records from One to all of the matches in Two. This behavior is pretty much a standard in all of the SQL variants (and you aren't using SAS Proc SQL but possibly Oracle?)

data one;
   input x y  ;
datalines;
1  1
1  2
1  3
2  4
;

data two;
  input x z ;
datalines;
1  100
1  200
2  6
;

proc sql;
   create table example as
   select a.x, a.y, b.z
   from one as a
        left join
        two as b
        on a.x=b.x
   ;
quit;

Solution: If your results are truly actual duplicates, as in every single resulting variable you could try adding the DISTINCT predicate as the start of the Select. Caution: there can be a lot of computational overhead with this instruction. That query already has a number of potential execution time flags with multiple subqueries in different places.

Other wise you need to reduce the duplicates earlier which could mean breaking that query up into smaller pieces.

topic Re: SQL QUERY DUPLICATES from Join Error? in New SAS User

SQL QUERY DUPLICATES from Join Error?

Re: SQL QUERY DUPLICATES from Join Error?