MS SQL: subselects - cryptic error "Invalid column name"

Question

When I run the following query to 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 is this happening? I do not understand why this would be wrong.

Answer 1

SQL Server does not allow aliases to be referenced by name at the same level. To fix this, redefine 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

Answer 2

The last line AND optin > 1is the intruder.

The clause WHEREknows nothing about the aliases of the columns in the SELECT list.

You should probably query this SELECT without a violation condition and apply this condition to an external SELECT.

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