1 Reply Latest reply: Aug 31, 2016 10:23 AM by CJ Kenny RSS

    Outer Join not working

    Ankit Agrawal

      Hello,

       

      I want to report the product quantities that we actually received against the product quantity that we were expecting. I have created a key (ASN_KEY) in both the expected and scanned tables. When I individually load each of the tables, the expected and scanned quantities are correct, but when I do an Outer join, Scanned quantity for one of the row(ASN_KEY - 3006829WD_13230_30_18440129) over writes the expected quantity for that row. Not sure why this is happening. Could you please help. Please find the screenshots attached.

       

      Expected vs Scanned.jpg

       

       

      ASN_Expected:

       

      LOAD

      RMA_NBR & '_' & CPG_ROW_ID & '_' & RTRNR_ID & '_' & ITM_CLNT_ID AS ASN_KEY,

      RTRNR_ID AS EXPECTED_STR_CLNT_ID,

      STR_NM AS EXP_STR_NM,

      PGM_TYP_CD AS EXP_PGM_TYP_CD,

      RMA_EXPECTED_QTY

      ;

       

       

      ASN_Scanned:

       

      LOAD

      RMA_NBR & '_' & CPG_ROW_ID & '_' & STR_CLNT_ID & '_' & ITM_ID AS ASN_SCANNED_KEY,

      CNTNR_PCS_ID,

      STR_CLNT_ID AS SCAN_STR_CLNT_ID,

      STR_NM AS SCAN_STR_NM,

      PGM_TYP_CD AS SCAN_PGM_TYP_CD,

      RMA_SCANNED_QTY

      ;

       

      OUTER JOIN (ASN_Expected)

       

      LOAD ASN_SCANNED_KEY AS ASN_KEY,

           CNTNR_PCS_ID,

           SCAN_STR_CLNT_ID,

           SCAN_STR_NM,

           SCAN_PGM_TYP_CD,

           RMA_SCANNED_QTY

      Resident ASN_Scanned

      ;


      Drop TABLE ASN_Scanned;