Case Condition to join columns

Question

Msg 156, Level 15, State 1, Line 8 Incorrect syntax near the keyword 'CASE'

I am trying to join columns based on the column value.

If case conditon 1 is true then i want to join ON im.ItemNumber = left(tr.itemnumber,len(tr.itemnumber)-4)

if Case condition 2 is true then

ON tr.ItemNumber = im.LegacyItemNumber

else

tr.ItemNumber = im.ItemNumber

    SELECT 
           im.Category as RootCategory,GETDATE() AS LoadDate,tr.*  
          into transactionreport_stage_testNN
    FROM transaction tr
    LEFT OUTER JOIN ALLDW_ora.dbo.Location(NOLOCK) loc ON tr.location=loc.location

    CASE 
    when
    right(tr.itemnumber,4) = '_old' then 
    LEFT OUTER JOIN [Inventory](NOLOCK) im
    ON im.ItemNumber = left(tr.itemnumber,len(tr.itemnumber)-4)


    when 
    tr.itemnumber <> im.ItemNumber 
    then
    LEFT OUTER JOIN [Inventory](NOLOCK) im
    ON tr.ItemNumber = im.LegacyItemNumber

else
LEFT OUTER JOIN [Inventory](NOLOCK) im
ON tr.ItemNumber = im.itemnumber

    end 

    AND im.Location=loc.LocationNo

Schema

[This](http://stackoverflow.com/questions/10256848/can-i-use-case-statement-in-a-join-condition/10260297#10260297) answer may be of some help. You can juggle the match condition in a `case`, but can't switch around other parts of the query, e.g. tables, willy nilly. You can still join everything and try to sort it out in a `where`, but there comes a point where things get out of hand. — HABO, Apr 08 '16 at 19:12
Please also note that such conditional joins are not recommended option and almost always can be replaced with some other approach like union. Sql is performing really great in set based operations, any structural code will perform worse. Such conditional logic may be seen as if...else. Please check this article: https://www.simple-talk.com/sql/performance/how-to-avoid-conditional-joins-in-t-sql/ for example. You will find more on the Internet. — Jakub Szumiato, Apr 08 '16 at 19:22

JamieD77 · Accepted Answer · 2016-04-08T19:28:01.793

0

You should probably just use AND's and OR's

LEFT OUTER JOIN [Inventory](NOLOCK) im ON im.Location=loc.LocationNo 
        AND (
            (right(tr.itemnumber,4) = '_old' AND im.ItemNumber = left(tr.itemnumber,len(tr.itemnumber)-4))
            OR 
            (tr.itemnumber <> im.ItemNumber AND tr.ItemNumber = im.LegacyItemNumber)
            OR
            (tr.ItemNumber = im.itemnumber)          
        )

it may just be as simple as

SELECT  im.Category AS RootCategory,
        GETDATE() AS LoadDate,
        tr.*
INTO    #transactionreport_stage_testNN
FROM    [transaction] tr
        LEFT OUTER JOIN ALLDW_ora.dbo.Location (NOLOCK) loc ON tr.location = loc.location
        LEFT OUTER JOIN [Inventory] (NOLOCK) im ON im.Location = loc.LocationNo
            AND REPLACE(tr.itemnumber, '_old', '') IN (im.itemnumber, im.legacyitemnumber)

edited Apr 08 '16 at 19:28

answered Apr 08 '16 at 18:49

JamieD77

13,796
1
17
27

Hey , Thanks for quick reply. I made change to code. – neoo Apr 08 '16 at 18:53
I have three conditions, If there are to conditions what you said is right. But I need to use different column for the one the condtion – neoo Apr 08 '16 at 18:55
oh i see what you're saying now.. just a sec – JamieD77 Apr 08 '16 at 18:59
AND tr.ItemNumber = CASE WHEN right(tr.itemnumber,4) = '_old' THEN left(tr.itemnumber,len(tr.itemnumber)-4) I need to join with im.itemnumber.... – neoo Apr 08 '16 at 18:59
Two make it simple im.itemnumber and im.legacyitemnumber – neoo Apr 08 '16 at 19:00
I have added schema image for better understading – neoo Apr 08 '16 at 19:07

Case Condition to join columns

1 Answers1