Re: Scope in derived tables
- From: Erland Sommarskog <esquel@xxxxxxxxxxxxx>
- Date: Fri, 29 Jun 2007 11:13:50 +0000 (UTC)
Stephen2 (Stephen@xxxxxxxxxxxxxx) 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@xxxxxxxxxxxxx
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
.
- References:
- Scope in derived tables
- From: Stephen2
- Scope in derived tables
- Prev by Date: Scope in derived tables
- Next by Date: Re: Output Column names in each Row along with the row Value
- Previous by thread: Scope in derived tables
- Index(es):
Relevant Pages
|
