[SOLVED] Using a function-generated column in the where clause

Using a function-generated column in the where clause

I have an SQL query, which calls a stored SQL function, I want to do this:

SELECT    dbo.fn_is_current('''{round}''', r.fund_cd, r.rnd) as [current]
FROM      BLAH
WHERE     current = 1

The select works fine, however, it doesn't know "current". Even though (without the WHERE) the data it generates does have the "current" column, and it's correct.

So, I'm assuming that this is a notation issue.

Solution

You cannot use an alias from the select in the where clause (or even again in the same select). Just use a subquery:

SELECT t.*
FROM (SELECT dbo.fn_is_current('''{round}''', r.fund_cd, r.rnd) as [current]
      FROM BLAH
     ) t
WHERE [current] = 1;

As a note: current is a very bad name for a column because it is a reserved word (in many databases at least, including SQL Server). The word is used when defining cursors. Use something else, such as currval.