Posted by Erland Sommarskog on 06/29/07 11:13

Stephen2 (Stephen@mailinator.com) writes:
> This is kind of what I'm trying to do in my MS SQL 2000 query. Should
> I be able to reference s1.col1 inside the 2nd derived table?
>
> I'm getting 'Invalid column name col1' and it's coming from the 2nd
> derived table (I've commented out other refs to just it to check).
>
> Maybe I need to use a temp table instead.
>
>
> SELECT s1.col1,
>
> (SELECT * FROM
>
> (SELECT COUNT(zzz) AS SomeTotal
> FROM tab1
> WHERE s1.col1 = zzz)) AS RowCount) /* error here */
>
> FROM
> (SELECT col1 FROM table) AS s1

Yes, you should be able to, and in SQL 2005 you are. But in SQL 2000 there
is a bug that gets in the way. It seems that you will have to resort to
a temp table.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]