1

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.

Brian Postow
  • 11,709
  • 17
  • 81
  • 125

1 Answers1

4

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.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • Thanks! Current is actually the name of the column we need (it's for "is this row current data, or historical data?") But thanks, I'll try to keep that at a minimum... – Brian Postow Mar 07 '16 at 22:08