Re: correct syntax for this select in SQL Server?

#1

Jim Lawton (usenet1 (AT) jimlawton (DOT) TAKEOUTinfo) writes:

Quote:

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 (AT) sommarskog (DOT) se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

#2

Derived tables must be assigned an alias.

select * from
(SELECT * from test) as X

Roy Harvey
Beacon Falls, CT

On Fri, 22 Jun 2007 21:19:31 GMT, Jim Lawton
<usenet1 (AT) jimlawton (DOT) TAKEOUTinfo> wrote:

Quote:

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 :-)

cheers,
Jim

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Re: correct syntax for this select in SQL Server?

comp.databases.ms-sqlserver comp.databases.ms-sqlserver

Re: correct syntax for this select in SQL Server? - 06-22-2007 , 05:30 PM

Re: correct syntax for this select in SQL Server? - 06-22-2007 , 05:33 PM