CODEWITHSUNDEEP
sqlsql-server-2014
Nick
Nick

Reputation: 372

IF conditonal criteria on Join or Where clause

I have a query I am writing that has a left outer join to a table that contains a ID Number field (STD_ID_NUM) I am displaying as a column. The table with the ID number field has key field SETID, VENDOR_ID, and VNDR_LOC. BUSINESS_UNIT is not a key field, however in some cases it is the only column that distinguishes one row from another. I was initially including Business Unit in my join from PS_GH_VNDR_ACCT_ID however because the Business Unit field is optional, there are some rows that don't have a value populated for Business Unit, and therefore my query does not return the STD_ID_NM.

As you can see Business Unit is NOT a required field in this table. In some cases like the first VENDOR_ID (GRAVED), the VNDR_LOC distinguishes the row, however in the case of the 2nd query with VENDOR_ID (SCIMED), Business Unit is the differentiator.

enter image description here

SELECT  A.BUSINESS_UNIT, E.DEPTID, A.PO_ID, B.SHIPTO_ID, A.VENDOR_ID, 
C.VENDOR_NAME_SHORT, A.PRICE_LOC, D.STD_ID_NUM 
FROM ((((PS_PO_HDR A 
LEFT OUTER JOIN  PS_PO_DFLT_TBL B ON  A.BUSINESS_UNIT = B.BUSINESS_UNIT AND 
 A.PO_ID = B.PO_ID ) 
LEFT OUTER JOIN  PS_VENDOR C ON  C.VENDOR_ID = A.VENDOR_ID ) 
LEFT OUTER JOIN  PS_GH_VNDR_ACCT_ID D ON  C.SETID = D.SETID AND C.VENDOR_ID 
 = D.VENDOR_ID  AND D.VNDR_LOC = A.VNDR_LOC )
LEFT OUTER JOIN  PS_PO_LINE_DISTRIB E ON  A.BUSINESS_UNIT = E.BUSINESS_UNIT 
 AND A.PO_ID = E.PO_ID ) 
WHERE ( A.PO_ID = 'K090000774')

My results are what I am expecting from the above query, I'm retrieving 1 row with STD_ID_NUM '839819059', but only because I am not joining PS_GH_VNDR_ACCT_ID on BUSINESS_UNIT.

enter image description here

If I add BUSINESS_UNIT to my join on the PS_GH_VNDR_ACCT_ID join table:

   LEFT OUTER JOIN  PS_GH_VNDR_ACCT_ID D ON  C.SETID = D.SETID AND 
   C.VENDOR_ID = D.VENDOR_ID  AND D.VNDR_LOC = A.VNDR_LOC AND  
   D.BUSINESS_UNIT = A.BUSINESS_UNIT)

Then I get a NULL value on the STD_ID_NUM column, because this particular vendor does not have a BUSINESS_UNIT specified.

However if I don't use BUSINESS_UNIT on my join then for this other Vendor (SCIMED) then I get several Duplicate rows, because there is nothing else to differentiate each row.

enter image description here

I'm thinking I need to use an IF/conditional statement to the join where if there is a BUSINESS_UNIT in the PS_GH_VNDR_ACCT_ID table for the row then join on BUSINESS_UNIT AND SETID AND VENDOR_ID AND VNDR_LOC , Otherwise if BUSINESS_UNIT is not populated for a row than join on SETID AND VENDOR_ID AND VNDR_LOC only.

Upvotes: 0

Views: 38

Answers (1)

Koen
Koen

Reputation: 734

One approach would be the following: Have an additional LEFT OUTER JOIN for the case where a null value exists in PS_GH_VNDR_ACCT_ID:

LEFT OUTER JOIN PS_GH_VNDR_ACCT_ID D 
       ON  C.SETID = D.SETID AND 
           C.VENDOR_ID = D.VENDOR_ID  AND 
           D.VNDR_LOC = A.VNDR_LOC AND 
           D.BUSINESS_UNIT = A.BUSINESS_UNIT)
LEFT OUTER JOIN PS_GH_VNDR_ACCT_ID D2 
       ON  C.SETID = D2.SETID AND 
           C.VENDOR_ID = D2.VENDOR_ID  AND 
           D2.VNDR_LOC = A.VNDR_LOC AND 
           D2.BUSINESS_UNIT IS NULL)

And modify the SELECT line:

SELECT  A.BUSINESS_UNIT, E.DEPTID, A.PO_ID, B.SHIPTO_ID, A.VENDOR_ID, C.VENDOR_NAME_SHORT, A.PRICE_LOC, D.STD_ID_NUM

To

SELECT A.BUSINESS_UNIT, 
       E.DEPTID, 
       A.PO_ID, 
       B.SHIPTO_ID, 
       A.VENDOR_ID, 
       C.VENDOR_NAME_SHORT, 
       A.PRICE_LOC, 
       COALESCE(D.STD_ID_NUM, D2.STD_ID_NUM)

This is all assuming you prefer the solution where BUSINESS_UNIT can be used to select a unique case to go first and go for the NULL as default.
Please note - without test data, I am not entirely certain this will work without issues.

Upvotes: 1

Related Questions