case statement in join | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

case statement in join

Can you use a case statement in an inner join? Based on the value in the IsReserveSku field, I need to use different fields to join on. Any help would be greatly appreciated. select storenum, rsvnacttype, count(*) as Cnt, sum(CertAmt) as Amt
from JournalCert JC
inner join test XT
on case when XT.IsReSku = ‘true’
then JC.rsvnsku = XT.Sku
else JC.rtlSKU = XT.Sku
end
where XT.AllStores = ‘true’
and JC.TransDate between ‘2007-01-01’ and ‘2007-06-01’
group by storenum, rsvnacttype Andy
Just to follow up….I think this will work. select storenum, rsvnacttype, count(*) as Cnt, sum(CertAmt) as Amt
from JournalCert JC
inner join test XT
on
XT.Sku = case when XT.IsReSku = ‘true’
then JC.rsvnsku
else JC.rtlSKU
end
where XT.AllStores = ‘true’
and JC.TransDate between ‘2007-01-01’ and ‘2007-06-01’
group by storenum, rsvnacttype I’m still validating the results, but it appears to work. Andy
]]>