MS SQL: Nested Selects - Mysterious "Invalid column name" error

When I run the following query against a MSSQL 2000

SELECT 
    DISTINCT(Email),
    (SELECT TOP 1 ActivityID
        FROM Activity aa, ActivityType tt 
        WHERE aa.ActivityTypeId = tt.ActivityTypeId 
            AND aa.ConsumerID = c.ConsumerID
            AND tt.ActivityType = 'Something_OptIn') optin,
    (SELECT TOP 1 ActivityID
        FROM Activity aa, ActivityType tt 
        WHERE aa.ActivityTypeId = tt.ActivityTypeId 
            AND aa.ConsumerID = c.ConsumerID
            AND tt.ActivityType = 'Something_OptOut') optout
FROM
    Activity a,
    Consumer c,
    ActivityType t
WHERE
    c.CountryID = '23'
    AND t.ActivityType = 'Something_Create'
    AND a.ActivityTypeId = t.ActivityTypeId
    AND c.ConsumerID = a.ConsumerID
    AND optin > 1

I get the following error

Server: Msg 207, Level 16, State 3, Line 1
Invalid column name '开发者_开发百科optin'.

Why does this happen? I can't see why it would be invalid.

SQL Server does not allow you to refer to aliases by name at the same level. To fix this, repeat the column definition:

WHERE
    c.CountryID = '23'
    AND t.ActivityType = 'Something_Create'
    AND a.ActivityTypeId = t.ActivityTypeId
    AND c.ConsumerID = a.ConsumerID
    AND (SELECT TOP 1 ActivityID
        FROM Activity aa, ActivityType tt 
        WHERE aa.ActivityTypeId = tt.ActivityTypeId 
            AND aa.ConsumerID = c.ConsumerID
            AND tt.ActivityType = 'Something_OptIn'
        ) > 1

Or use a subquery:

SELECT  *
FROM    (
        SELECT 
            DISTINCT(Email),
            (...) optin,
            (...) optout
        FROM
            Activity a,
            Consumer c,
            ActivityType t
        ) as SubqueryAlias
WHERE
    c.CountryID = '23'
    AND t.ActivityType = 'Something_Create'
    AND a.ActivityTypeId = t.ActivityTypeId
    AND c.ConsumerID = a.ConsumerID
    AND optin > 1

The last line AND optin > 1 is the offender.

The WHERE clause knows nothing about column aliases in the SELECT list.

You should probably subquery this SELECT without the offending condition, and apply that condition to the outer SELECT.

SELECT *
FROM (
  SELECT
  ...
  WHERE ... /* everything except 'optin > 1' */
) anyAlias
WHERE optin > 1