SQL Server - select from (select union select)

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

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

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

    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?

Hy,

Try:

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

Comment: Alias to table(select).

Bye.