MSSQL: Problems with named case in select

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

MSSQL: Problems with named case in select

Author

Topic

tiwas
Starting Member

37 Posts

Posted - 2012-04-28 : 14:24:13

Hi guys,
I'm trying to select some users from a database based on membership. In order to do so, I've made the following select statement
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 current_member = '1'

the table and column names have been changed - I certainly wouldn't use that in a production datbase, but I doubt my customer would like to see their real table/column names on the internet :p )

The reason for the where clause is to change it to where (@getall = 1) or (current_member = '1') in a sproc.

In any case, I'm getting "Msg 207, Level 16, State 1, Line 8
Invalid column name 'current_member'."

Could someone please point me in the right direction here?

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-04-28 : 17:06:15

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) >= 0

Another 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.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources