SQL-Server left outer join with case not stopping at first when

Question

I want to get a row that matches the first matching "when" in a case expression in the 'on' clause of a left outer join, but instead I get rows from EVERY when that is matched.
The internet tells me this is impossible, that a case will always stop at the first matching when.

SELECT MILL_ORDER_NUMBER
       ,SHORTY_NAME
       ,PRIMARY_DEST
       ,ALT_DESTINATION
       ,CB.CDE_CNSUM_LOC as CB4V_CNSUM_LOC
       ,CB.CDE_DEST
       ,CB.NAM_CUST_SHTY
FROM HLFOR01A OA 

left outer join (select CDE_CNSUM_LOC, CDE_DEST, NAM_CUST_SHTY from CSAR_CB4V0023) CB

on case
when ((OA.SHORTY_NAME = CB.NAM_CUST_SHTY) and (substring(OA.PRIMARY_DEST,1,1) < 'A') and (OA.PRIMARY_DEST = CB.CDE_DEST)) then 1

when ((OA.SHORTY_NAME = CB.NAM_CUST_SHTY) and (CB.CDE_DEST = (select min(dd.CDE_DEST) from CSAR_CB4V0023 dd where dd.NAM_CUST_SHTY = OA.SHORTY_NAME))) then 1
else 0  end = 1
where MILL_ORDER_NUMBER = '84220631'

If both when clauses exist, I get

MILL_ORDER_NUMBER SHORTY_NAME PRIMARY_DEST ALT_DESTINATION CB4V_CNSUM_LOC CDE_DEST NAM_CUST_SHTY

84220631      CMPNY1    5U              1641        00      CMPNY1 <-- matches 2nd when clause

84220631      CMPNY1    5U              1627        5U      CMPNY1 <-- matches 1st when clause

If I comment out the 1st when clause I only get the first row. If I comment out the 2nd when clause, I only get the 2nd row.
I don't understand why it doesn't stop at the first when clause that matches?

Side note: you want to avoid function calls (like `SUBSTRING`) and correlated subqueries (like `(SELECT MIN(..) ....`) - oh, and implicit conversions, such as when one column is `INTEGER` and the other `CHAR` - if at all possible. — Clockwork-Muse, Jan 20 '18 at 00:47
Thank you jonsca for reformatting! I've read through the formatting rules to prevent that in the future. — Georgia May, Jan 22 '18 at 19:58
I can get rid of the min (change to top 1 and order by dest asc) and the substring. I'll suggest a vb.net solution though, due to the other join and inordinate amount of logic in the joins. I thought the on case would work after having read this [https://stackoverflow.com/questions/10256848/can-i-use-case-statement-in-a-join-condition](https://stackoverflow.com/questions/10256848/can-i-use-case-statement-in-a-join-condition) It sounded like it would stop at the first match and I didn't understand about it actually looping through every row in the outer table. — Georgia May, Jan 22 '18 at 20:03

Andrew · Answer 1 · 2018-01-20T00:45:59.470

When joining, all rows on one side of the join are evaluated against all rows on the other side of the join.

Your case statement is stopping at the first match, for each pair of rows on either side. Just because one of the rows on the left has already matched a row on the right, does not stop it from matching another row on the right, using either of the cases in your case statement as each pair is evaluated independently of any existing matches. Your case statement is effectively equivalent to, though less efficient than:

on OA.SHORTY_NAME = CB.NAM_CUST_SHTY
    and ((substring(OA.PRIMARY_DEST,1,1) < 'A' and OA.PRIMARY_DEST = CB.CDE_DEST) 
    or (CB.CDE_DEST = (select min(dd.CDE_DEST) from CSAR_CB4V0023 dd where dd.NAM_CUST_SHTY = OA.SHORTY_NAME))

Think of it as a for loop nested in another for loop, executing your case statement on every possible pair of rows.

DECLARE @Table (MILL_ORDER_NUMBER {type}, SHORTY_NAME {type}, PRIMARY_DEST {type}, ALT_DESTINATION {type}, CB4V_CNSUM_LOC {type}, CDE_DEST {type}, NAM_CUST_SHTY {type})

INSERT INTO @Table (MILL_ORDER_NUMBER ,SHORTY_NAME ,PRIMARY_DEST ,ALT_DESTINATION ,CB4V_CNSUM_LOC, CDE_DEST, NAM_CUST_SHTY)
SELECT MILL_ORDER_NUMBER, SHORTY_NAME, PRIMARY_DEST, ALT_DESTINATION, CB.CDE_CNSUM_LOC, CB.CDE_DEST, CB.NAM_CUST_SHTY
FROM HLFOR01A OA 
JOIN (select CDE_CNSUM_LOC, CDE_DEST, NAM_CUST_SHTY from CSAR_CB4V0023) CB
on ((OA.SHORTY_NAME = CB.NAM_CUST_SHTY) and (CB.CDE_DEST = (select min(dd.CDE_DEST) from CSAR_CB4V0023 dd where dd.NAM_CUST_SHTY = OA.SHORTY_NAME)))
where MILL_ORDER_NUMBER = '84220631'

INSERT INTO @Table (MILL_ORDER_NUMBER ,SHORTY_NAME ,PRIMARY_DEST ,ALT_DESTINATION ,CB4V_CNSUM_LOC, CDE_DEST, NAM_CUST_SHTY)
SELECT MILL_ORDER_NUMBER, SHORTY_NAME, PRIMARY_DEST, ALT_DESTINATION, CB.CDE_CNSUM_LOC, CB.CDE_DEST, CB.NAM_CUST_SHTY
FROM HLFOR01A OA 
JOIN (select CDE_CNSUM_LOC, CDE_DEST, NAM_CUST_SHTY from CSAR_CB4V0023) CB
on ((OA.SHORTY_NAME = CB.NAM_CUST_SHTY) and (substring(OA.PRIMARY_DEST,1,1) < 'A') and (OA.PRIMARY_DEST = CB.CDE_DEST))
where MILL_ORDER_NUMBER = '84220631' AND NOT EXISTS (SELECT top 1 1 FROM @table t where t.MILL_ORDER_NUMBER=OA.MILL_ORDER_NUMBER)

INSERT INTO @Table (MILL_ORDER_NUMBER ,SHORTY_NAME ,PRIMARY_DEST ,ALT_DESTINATION)
SELECT MILL_ORDER_NUMBER, SHORTY_NAME, PRIMARY_DEST, ALT_DESTINATION
FROM HLFOR01A OA 
where MILL_ORDER_NUMBER = '84220631' AND NOT EXISTS (SELECT top 1 1 FROM @table t where t.MILL_ORDER_NUMBER=OA.MILL_ORDER_NUMBER)

SELECT MILL_ORDER_NUMBER ,SHORTY_NAME ,PRIMARY_DEST ,ALT_DESTINATION ,CB4V_CNSUM_LOC, CDE_DEST, NAM_CUST_SHTY FROM @Table

Well shoot. That bursts my bubble. I'll have to come up with some other convoluted solution to give me the row where shorty & dest match, or give me the min row where shorty matches, but only if there's not a matching dest. Which isn't so bad by itself, but there's a lot more to the SQL, and more left joins and cases. But **THANK YOU** for answering - that wasn't how I'd interpreted the left join with a case in the ON clause. — Georgia May, Jan 20 '18 at 00:28
I added a potential solution, though Obie's answer is better if you will only ever want one row back. My solution inserts all matching rows from the first case into a table variable, then inserts from the second case where the `MILL_ORDER_NUMBER` was not added by the first, then finally adds just from the first table if no rows were created by the first two (simulating the left outer join) This would work even if you didn't have the single `MILL_ORDER_NUMBER` where clause, unlike Obie's answer, which relies on only taking the top row. — Andrew, Jan 20 '18 at 00:45

score 1 · Answer 2 · answered Jan 20 '18 at 00:31

1

This case will yield a set of records that match either of the when's that result in a 1. To get the first matching record you could do a top n:

SELECT TOP 1 MILL_ORDER_NUMBER ...

A group by could get you down to 1 result row with min or max on CB4V_CNSUM_LOC,CB.CDE_DEST,CB.NAM_CUST_SHTY but you may be mixing these from multiple records so this is probably not what you want.

A tweak on the first option would be to "weight" each of the whens of your case so that you will get a row that matches the first when if it exists:

SELECT TOP 1 MILL_ORDER_NUMBER ...
...
ORDER BY
case
when ((OA.SHORTY_NAME = CB.NAM_CUST_SHTY) and (substring(OA.PRIMARY_DEST,1,1) < 'A') and (OA.PRIMARY_DEST = CB.CDE_DEST)) 
then 1
when ((OA.SHORTY_NAME = CB.NAM_CUST_SHTY) and (CB.CDE_DEST = (select min(dd.CDE_DEST) from CSAR_CB4V0023 dd where dd.NAM_CUST_SHTY = OA.SHORTY_NAME))) 
then 2
else 99  end

answered Jan 20 '18 at 00:31

Obie

447
2
5

`on OA.SHORTY_NAME = CB.NAM_CUST_SHTY` should be how you join for this, since it is the one thing common to both case statements, the rest becomes your order by. Though that does run the risk of a match that doesn't match either case being what is returned. – Andrew Jan 20 '18 at 00:50
Sorry not to have responded earlier - I'm in the middle of a horrible case of the flu. This is cool! And works. :) But there's a 2nd left outer join, and it's got 8 when clauses. I think I should develop a vb.net app to create a table of order plus consuming loc plus person responsible once a day (because that info doesn't change often enough to need more frequent updates) and then let my DBA merge that with the inventory table - simple inner join, no dups would result. – Georgia May Jan 22 '18 at 19:29
The DBA is creating a view to duplicate the results of a customer spreadsheet created by SAS from Oracle and SAS databases and then combined with several DB2 tables. Too much logic involved. The order table plus comsuming loc plus person responsible (the 8 when clauses) changes infrequently enough that a vb.net app can create it, and then the DBA view can join it with inventory one-for-one on order number. – Georgia May Jan 22 '18 at 19:32
Sounds like that will simplify things for you quite a bit. Best of luck. – Obie Jan 23 '18 at 03:37

score 0 · Answer 3 · answered Jan 20 '18 at 00:46

Does this work for you?

SELECT TOP 1 MILL_ORDER_NUMBER
       ,SHORTY_NAME
       ,PRIMARY_DEST
       ,ALT_DESTINATION
       ,CB.CDE_CNSUM_LOC as CB4V_CNSUM_LOC
       ,CB.CDE_DEST
       ,CB.NAM_CUST_SHTY
FROM HLFOR01A OA 
left outer join (select CDE_CNSUM_LOC, CDE_DEST, NAM_CUST_SHTY from CSAR_CB4V0023) CB on 
    ((OA.SHORTY_NAME = CB.NAM_CUST_SHTY) and (substring(OA.PRIMARY_DEST,1,1) < 'A') and (OA.PRIMARY_DEST = CB.CDE_DEST)) 
    or
    ((OA.SHORTY_NAME = CB.NAM_CUST_SHTY) and (CB.CDE_DEST = (select min(dd.CDE_DEST) from CSAR_CB4V0023 dd where dd.NAM_CUST_SHTY = OA.SHORTY_NAME)))
where MILL_ORDER_NUMBER = '84220631'

I realize some of the parens are superfluous but kept them in for the sake of consistency. I'm not 100% sure this is what you're after, but...

The TOP 1 will get you the first result (though without an ORDER BY clause, it's arbitrary which result will be first).

The new ON clause is a bit cleaner and I think represents what you're going for; and if it isn't, it should at least be easier to visualize and manipulate.

I hope this helps.

The order matters for what Georgia wants, that was the idea in the case, to match the first case where possible, if there are no matches then match on the second case. Obie's answer handles that by weighting the results. If you want to clean up the on clause `OA.SHORTY_NAME = CB.NAM_CUST_SHTY` is on either side of the or and can just be pulled out and anded to the rest. — Andrew, Jan 20 '18 at 00:52
Sorry not to have responded earlier - I'm in the middle of a horrible case of the flu. I do need the weighting, but thank you for looking at my question! — Georgia May, Jan 22 '18 at 19:02

SQL-Server left outer join with case not stopping at first when

3 Answers3