Posted by Jim Lawton on 06/23/07 08:43

On Sat, 23 Jun 2007 06:32:00 GMT, Jim Lawton
<usenet1@jimlawton.TAKEOUTinfo> wrote:

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

• Next in forum: Re: BULK INSERT ignores UNIQUE index with IGNORE_DUP_KEY set?
• Prev in forum: Re: correct syntax for this select in SQL Server?
• Thread view: Re: correct syntax for this select in SQL Server?

[Reply to this message]