SQL QUERY DUPLICATES from Join Error?

[amartin1]

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) ))

[ballardw]

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.