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
]]>

Software Reviews | Book Reviews | FAQs | Tips | Articles | Performance Tuning | Audit | BI | Clustering | Developer | Reporting | DBA | ASP.NET Ado | Views tips | | Developer FAQs | Replication Tips | OS Tips | Misc Tips | Index Tuning Tips | Hints Tips | High Availability Tips | Hardware Tips | ETL Tips | Components Tips | Configuration Tips | App Dev Tips | OLAP Tips | Admin Tips | Software Reviews | Error | Clustering FAQs | Performance Tuning FAQs | DBA FAQs |