test test test test - 7 months ago 24
SQL Question

subquery case when

I have the following query

SELECT Case
when V.[Code Article] in
(SELECT [Code Article]
FROM [Prix Revient MPX CMR] c
WHERE CONVERT(DATE, V.[Date Time]) = [DATE] and [CMR] = V.[Code Site] )
then c.[Prix de revient réel]
ELSE V.[Prix de Revient]
end as [Prix de Revient]
FROM dbo.[Sales] V


I get the following error:


The multi-part identifier "c.Prix de revient réel" can not be bound


How to modify it ?

Answer

If you formatted the query like this:

SELECT (Case when V.[Code Article] in (SELECT c.[Code Article]
                                       FROM [Prix Revient MPX CMR]  c
                                       WHERE CONVERT(DATE, V.[Date Time]) =  c.[DATE] and c.[CMR] = V.[Code Site]
                                     )
             then c.[Prix de revient réel] 
             ELSE V.[Prix de Revient]  
        end) as [Prix de Revient]
FROM  dbo.[Sales] V;

Then the problem would be obvious. There is no c for the then clause. Presumably you want a join:

select coalesce(c.[Prix de revient réel], V.[Prix de Revient]) as [Prix de Revient]
from dbo.[Sales] V left join
     [Prix Revient MPX CMR]  c
     on CONVERT(DATE, V.[Date Time]) =  c.[DATE] and
        c.[CMR] = V.[Code Site] and
        V.[Code Article] = c.[Code Article]
Comments