SQL Server - select from (select union select)

Asked By Niron Kag on 21-May-07 04:38 AM

hi,

I am trying to do something like this

select *  from (select ... from ... where ... UNION select ... from ... where...)

but I  get the message :

Incorrect syntax near ')'.

why cant I do that? thanks alot...

Response - F Cali replied to Niron Kag on 21-May-07 10:59 AM

You have to provide a table alias to your sub-query:

select *  from (select ... from ... where ... UNION select ... from ... where...) A

SQL Server Helper
http://www.sql-server-helper.com/tips/date-formats.aspx

Thanks F Cali good to know - Niron Kag replied to F Cali on 21-May-07 02:13 PM

Thanks

still i am getting the same error! - srini vasan replied to Niron Kag on 02-Jun-08 07:22 AM

    SELECT MIN(dbo.FS_ItemInventory.Bin) FROM
(SELECT MIN(dbo.FS_LotTrace.LotNumber) FROM
dbo.FS_LotTrace RIGHT OUTER JOIN dbo.FS_MOHeader AS h INNER JOIN
dbo.FS_MOLine AS l ON l.MOHeaderKey = h.MOHeaderKey INNER JOIN
dbo.FS_Item AS i ON i.ItemKey = l.ItemKey INNER JOIN
dbo.FS_MOLineData ON l.MOLineKey = dbo.FS_MOLineData.MOLineKey LEFT OUTER JOIN
dbo.FS_DemandSupply AS ds ON l.MOLineKey = ds.TopLevelDemandSupplyKey LEFT OUTER JOIN
dbo.FS_ItemInventory RIGHT OUTER JOIN
dbo.FS_Item AS di ON dbo.FS_ItemInventory.ItemKey = di.ItemKey and ds.DemandItemKey = di.ItemKey  and
dbo.FS_LotTrace.LotTraceKey = dbo.FS_ItemInventory.LotTraceKey
 where (dbo.FS_ItemInventory.InventoryCategory!='H' or dbo.FS_ItemInventory.InventoryCategory is null) group by di.ItemNumber)
 where (dbo.FS_ItemInventory.InventoryCategory!='H' or dbo.FS_ItemInventory.InventoryCategory is null) group by di.ItemNumber,dbo.FS_LotTrace.LotNumber

still ia am getting the same error!

Incorrect syntax near  'where'




why cant I do that? thanks alot..
is it correct? - srini vasan replied to Niron Kag on 02-Jun-08 05:47 AM
    hi!

  i want to use IN query like

select ... from ...
where field1 in (...)
and field2 in (....)

when i write query like this, the result is display.
but its wrong.
is it correct?
Correct - Willy Viana replied to Niron Kag on 03-Jun-09 02:48 PM

Hy,

Try:

select *  from (select ... from ... where ... UNION select ... from ... where...) TEST

Comment: Alias to table(select).

Bye.