An expression of non-boolean type specified in a context where a condition is expected, near 'end'. error

Question

select *
from DBNAME.dbo.Shell_V36 V
inner join 
DBNAME.DBO.Promo P 
on 
Case  
when 
(V.Product = P.From_Product_Code) 
And  (V.cur_date >= P.Promo_end_date)
or (V.cur_date <= P.Promo_start_date)
then (P.To_Product_Code)
else(P.From_Product_Code)
end 

i am getting "An expression of non-boolean type specified in a context where a condition is expected, near 'end'." can any one help me out please.

Answer 1

You have to provide the field from Shell_V36 table used in the ON clause of the JOIN operation:

select *
from DBNAME.dbo.Shell_V36 V
inner join DBNAME.DBO.Promo P 
on Case when 
           (V.Product = P.From_Product_Code) And  
           (V.cur_date >= P.Promo_end_date)
           or 
           (V.cur_date <= P.Promo_start_date)
         then (P.To_Product_Code)
         else (P.From_Product_Code)
   End = V.code -- presumably code field should be used