correct syntax for this select in SQL Server?

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
--
Jim
a Yorkshire polymothJim Lawton (usenet1@.jimlawton.TAKEOUTinfo) writes:

Quote:

Originally Posted by

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/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||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@.jimlawton.TAKEOUTinfowrote:

Quote:

Originally Posted by

>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

|||On Fri, 22 Jun 2007 21:30:17 +0000 (UTC), Erland Sommarskog
<esquel@.sommarskog.sewrote:

Quote:

Originally Posted by

>Jim Lawton (usenet1@.jimlawton.TAKEOUTinfo) writes:

Quote:

Originally Posted by

>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

Thanks to both you and Roy.
--
Jim
a Yorkshire polymoth|||On Sat, 23 Jun 2007 06:32:00 GMT, Jim Lawton
<usenet1@.jimlawton.TAKEOUTinfowrote:

Quote:

Originally Posted by

>On Fri, 22 Jun 2007 21:30:17 +0000 (UTC), Erland Sommarskog
><esquel@.sommarskog.sewrote:
>

Quote:

Originally Posted by

>>Jim Lawton (usenet1@.jimlawton.TAKEOUTinfo) writes:

Quote:

Originally Posted by

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

>

And, actually, and for the record, let's just add, that for this to work
on Oracle we have to omit the "as" - which is OK for SQL Server as well.
--
Jim
a Yorkshire polymoth