Unrecognized column name that I define

Question

I have the SQL statement below and it works:

SELECT T1.*,
       (SELECT COUNT(T3.CTransaction) FROM GBTransaction T3 
        WHERE T3.CSite = 'Store' 
        and   T3.CTransactionType = 'Release' 
        and   T3.CReference = T1.CTransaction) as releasecount 
FROM   GBTransaction T1 
where  T1.CSite = 'Store' 
and    T1.CTransactionType = 'Layaway' 
and    T1.CStatus = 'Active' 
and    T1.CBalance <= 0 
order by T1.CBalance, T1.CDueDate, T1.CTransaction

Basically I'm determining if there is a reference record existing through (SELECT COUNT(T3.CTransaction) FROM GBTransaction T3 WHERE T3.CSite = 'Store' and T3.CTransactionType = 'Release' and T3.CReference = T1.CTransaction) as releasecount

But if I add another WHERE condition which is AND releasecount > 0, SQL Server says

Invalid column name releasecount.

The final statement including the added WHERE condition is the one below:

SELECT  T1.*,
        (SELECT COUNT(T3.CTransaction) FROM GBTransaction T3 
         WHERE  T3.CSite = 'Store' 
         and    T3.CTransactionType = 'Release' 
         and    T3.CReference = T1.CTransaction) as releasecount 
FROM    GBTransaction T1 
where   T1.CSite = 'Store' 
and     T1.CTransactionType = 'Layaway' 
and     T1.CStatus = 'Active' 
and     T1.CBalance <= 0 
and     releasecount > 0 
order by T1.CBalance, T1.CDueDate, T1.CTransaction

Why doesn't this work?

Answer 1

You are attempting to use a "column alias" in the where clause of the same query. This isn't possible in SQL Server (and in many other databases). Without going into enormous detail, the clause order of select to order by is NOT the way the query is executed - which starts with the from & where clauses. So in the where clause (executed before the select clause) the column alias can't be referenced.

There is another way to execute that "correlated sub-query" using an "apply operator". Here we can use a cross apply. Two advantages of this approach are:

• it is generally faster than the approach in your original query, and
• because apply operators are part of the from clause you can include the column alias in the subsequent where clause.

    SELECT T1.*
        , oa.releasecount
    FROM GBTransaction T1
    CROSS APPLY (
        SELECT COUNT(T3.CTransaction) AS releasecount
        FROM GBTransaction T3
        WHERE T3.CSite = 'Store'
            AND T3.CTransactionType = 'Release'
            AND T3.CReference = T1.CTransaction
        ) oa
    WHERE T1.CSite = 'Store'
        AND T1.CTransactionType = 'Layaway'
        AND T1.CStatus = 'Active'
        AND T1.CBalance <= 0
        AND oa.releasecount > 0
    ORDER BY T1.CBalance
        , T1.CDueDate
        , T1.CTransaction1