Posted by Erland Sommarskog on 06/22/07 21:30

Jim Lawton (usenet1@jimlawton.TAKEOUTinfo) writes:
> This (demo) statement is fine in Access, and so far as I can see, should
> be OK in SQL Server.
>
> But Enterprise Manager barfs at the final bracket. Can anyone help
> please?
>
> select sum(field1) as sum1, sum(field2) as sum2 from
> (SELECT * from test where id < 3
> union
> SELECT * from test where id > 2)
>
> In fact, I can reduce it to :-
>
> select * from
> (SELECT * from test)
>
> with the same effect - clearly I just need telling :-)

In SQL Server, you need to provide an alias for the derived table:

SELECT * FROM (SELECT *FROM test) AS x

--
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]