Logically SQL Server evaluates the SELECT clause after performing the WHERE clause. This means aliases you define in the select clause are not available for use in the WHERE clause. So you should do the following:SELECT CASE
WHEN DATEDIFF(d, GETDATE(), medlem_til) < 0 THEN 0
ELSE 1
END AS current_member,
table2.*,
table3.*
FROM table1
LEFT OUTER JOIN table2
ON table1.member_id = table2.member_id
LEFT OUTER JOIN table3
ON table1.branch_id = table2.branch_id
WHERE DATEDIFF(d, GETDATE(), medlem_til) >= 0Another thing to keep in mind is the following: If you do what I suggested above and if medlem_til is a column in table2 or table3 (rather than table1), that effectively forces the join on that table to an INNER JOIN instead of a LEFT JOIN.
MSSQL: Problems with named case in select
Posted - 04/28/2012 : 14:24:13
