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