question

tstrole avatar image
tstrole asked

LEFT JOIN STATEMENT RETURNING NULL

Hello, I am trying to decipher differing results on two queries, relating to one specific left join. The more complex query is returning NULL in the 'Line_Descriptions' column, but I am expecting some text. The 2nd query is paired down to include just one left join to return the 'Line_Descriptions' and it works. What could be the cause of this? Simple example that works: SELECT T1.Line_Descriptions, T1.IND, T2.* FROM CCSDTA.DPMORDM3 T2 LEFT JOIN ETL.Line_Descriptions T1 ON T1.PONBR = T2.PONBR AND T1.POLNNBR = T2.POLNNBR AND T1.RLSENO = T2.RLSENO WHERE T2.PONBR = 'CA0000030833' AND T2.POLNNBR = 8 AND T2.RLSENO = 0 ; Returns: "BALANCE OF P.O./************************" in the Line Descriptions column. More complex query , returns NULL in the Line Descriptions column for table E6. SELECT E6.Line_Descriptions, E4.PCN, Cast(T1.PONBR as varchar) "PO No", E1.[Supplier Code] as "PO Supplier Code", '' as "PO Date", CASE When T2.POTYP in ('TL','TS','TP') Then 'Tooling' When T2.POTYP = 'CA' Then 'Capital' When T2.POTYP in ('KR','BR','RG') Then 'Production' When T2.POTYP = 'S' Then 'Services' When T2.POTYP = 'PP' Then 'Pre-Production' When T2.POTYP = 'AD' Then 'Add-on' When T2.POTYP in ('MA','MF','MP','MS','ME','MT') Then 'Supply MRO' When T2.POTYP in ('HQ','FH','NC') Then 'Services' Else ' ' END "PO Type Code", 'On Order' as "PO Status Code", ' ' as "PO Contact Last Name", ' ' as "PO Contact First Name", T7.BYRID as "ASI Buyer ID", T9.Last_Name as "PO Issued By Last Name", T9.First_Name as "PO Issued By First Name", CASE When T2.FRTCRY = 'UPS' then 'UPS' When T2.FRTCRY = 'NAL' then 'NAL Freight' When T2.FRTCRY = 'VT' then 'Vendor Truck' When T2.FRTCRY = 'FEDX' then 'FedEx' When T2.FRTCRY = 'YRCL' then 'Milk Run Truck' When T2.FRTCRY = 'PU' then 'NAL Freight' When T2.FRTCRY = 'UPSR' then 'UPS' When T2.FRTCRY = 'AIR' then 'FedEx' Else 'Milk Run Truck' END "PO Carrier Code", CASE WHEN T1.PLT = 'Paris' then 'PAR' WHEN T1.PLT = 'Salem' then 'SAL' WHEN T1.PLT = 'Flora' then 'FLA' WHEN T1.PLT = 'Alabama' then 'MUS' WHEN T1.PLT = 'CORP' then 'PAR' END as "PO Ship To Code", CASE When T2.FOB = 'S' then 'Origin' When T2.FOB = 'D' then 'Destination' END "PO FOB Code", E5.Terms as "PO Terms Code", ' ' as "PO Printed Note", ' ' as "PO Internal Note", CASE WHEN T2.MCCODE = 'YEN' then 'JPY' WHEN T2.MCCODE = 'EU' then 'EUR' ELSE T2.MCCODE END "PO Currency Code", T1.POLNNBR as "Line No", ' 'as "Line Part No", 1 as "Line Part Revision", ' ' as "Line Operation", ' ' as "Line Item No", ' ' as "Line Tool No", ' ' as "Line Material Code", ' ' as "Line Supplier Part No", E6.IND, E6.Line_Descriptions as "Line Description", ' ' as "Line Internal Note", T1.UNITPRICE as "Line Unit Price", CASE WHEN T1.UOM = 'EA' then 'piece(s)' WHEN T1.UOM = 'LB' then 'Lbs' WHEN T1.UOM = 'GRAM' then 'grams(s)' WHEN T1.UOM = 'GL' then 'gallon(s)' WHEN T1.UOM = 'IN' then 'inch(es)' WHEN T1.UOM = 'FT' then 'feet' END "Line_Price_Unit", CASE WHEN T1.UOM = 'EA' then 'piece(s)' WHEN T1.UOM = 'LB' then 'Lbs' WHEN T1.UOM = 'GRAM' then 'grams(s)' WHEN T1.UOM = 'GL' then 'gallon(s)' WHEN T1.UOM = 'IN' then 'inch(es)' WHEN T1.UOM = 'FT' then 'feet' END "Line Order Unit", CASE When E4.[plex full] = ' ' then 'blank' ELSE E4.[plex full] END "Line Account No", 'Non-Taxable' as "Line Tax Code", ' ' as "Line Job Code", ' ' as "Line Expense Project Code", T2.RLSENO as "Release No", T4.USADAT as "Release Due Date", (T1.QTYAUT - T1.QTYINSVER - T1.QTYVCH) as "Release Quantity", T1.QTYRCV as "Receipt Quantity", ' ' as "Line Accounting Job No", '12/23/2015' as "PO Due Date", 'Y' as "PO Approved", ' ' as "PO Building Code", CASE When SUBSTRING(T1.PONBR,1,2) in ('BR','KR','KB') then 'Y' ELSE 'N' END "PO Blanket", ' ' AS "PO Blanket Date Begin", ' ' as "PO Blanket Date End", ' ' as "Supplier Acknowledgement No", ' ' as "Add By User ID", 'Firmed' as "Release Status", E1.[Supplier Address Code] as "Supplier Address Code", ' ' as "Manufacturer Code", ' ' as "Manufacturer Part No", ' ' as "Manufacturer Part Revision", ' ' as "INCO Terms", ' ' as "Negotiated Place", ' ' as "PO Category", 'Ground' as "PO_Ship_Via", T9.Last_Name as "Responsible_Last_Name", T9.First_Name as "Responsible_First_Name", ' ' as "Department_Code", ' ' as "Service_Code", 0 as "Consignment", CASE When T2.FRTMSG = 'COD' then 'COD' When T2.FRTMSG = 'COL' then 'Collect' When T2.FRTMSG = 'PPD' then 'Prepaid' ELSE '' END "PO_Freight_Terms", 0 as "Line_Master_Price_Use", ' ' as "Line_Item_Note", ' ' as "PO_Issued_By_User_ID", ' ' as "Responsible_User_ID" FROM CCSDTA.DPMORDM3 T1 LEFT JOIN CCSDTA.DPMORDM0 T2 ON T1.PONBR = T2.PONBR AND T1.RLSENO = T2.RLSENO LEFT JOIN IQDATE.IQDATE T4 ON T1.NEEDDTE = T4.JULAN2 LEFT JOIN IQDATE.IQDATE T5 ON T2.ISSDTE = T5.STDDAT LEFT JOIN CCSDTA.DCSDIM T7 ON T1.PLT = T7.PLT AND T1.ITMID = T7.ITMID LEFT JOIN CCSDTA.DPMVNDR1 T10 ON T1.VNDID = T10.VNDID LEFT JOIN ETL.BUYER T9 ON T2.BYRID = T9.BuyerID JOIN ETL.Supplier_CrossReference E1 ON T1.VNDID = E1.[Old Supplier Code] LEFT JOIN ETL.full_acct_xref E4 ON T1.CHGTOACT = E4.[Full NAL] LEFT JOIN ETL.PLEX_Supplier_Terms E5 ON T1.VNDID = E5.[Old Supplier Code] LEFT JOIN ETL.Line_Descriptions E6 ON T1.PONBR = E6.PONBR AND T1.RLSENO = E6.RLSENO AND T1.POLNNBR = E6.POLNNBR AND T1.PONBR = 'CA0000030833' --I have also had this below in the WHERE clause , with the same result. AND E5.[Plexus Customer No] = '288822' AND E1.[Plexus Customer No] = '288822' WHERE E1.[Supplier Address Code] like '%Main%' AND E4.PCN = 'FLA';
sql 2012
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

· Write an Answer
tstrole avatar image
tstrole answered
I have resolved my own question...I misplaced two statements, that should have been in the WHERE clause, but were within a LEFT join. AND E5.[Plexus Customer No] = '288822' AND E1.[Plexus Customer No] = '288822'
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.