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 polymoth

Re: correct syntax for this select in SQL Server?

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/downlo ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books .mspx

Re: correct syntax for this select in SQL Server?

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

>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

Re: correct syntax for this select in SQL Server?

On Fri, 22 Jun 2007 21:30:17 +0000 (UTC), Erland Sommarskog
wrote:

>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


Thanks to both you and Roy.
--
Jim
a Yorkshire polymoth

Re: correct syntax for this select in SQL Server?

On Sat, 23 Jun 2007 06:32:00 GMT, Jim Lawton
wrote:

>On Fri, 22 Jun 2007 21:30:17 +0000 (UTC), Erland Sommarskog
> wrote:
>
>>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
>

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