select from select? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

select from select?

Author

Topic

sundarsrini_s
Starting Member

6 Posts

Posted - 2008-06-02 : 03:12:04

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 ON ds.DemandItemKey = di.ItemKey ON
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

but i get a error

Incorrect syntax near the keyword 'where'.

i also tried by using alias name.but it wont work

what can i do for this?

nr
SQLTeam MVY

12543 Posts

Posted - 2008-06-02 : 03:15:33

where (dbo.FS_ItemInventory.InventoryCategory!='H' or dbo.FS_ItemInventory.InventoryCategory is null) group by di.ItemNumber) a

You need to give the derived table an alias.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-02 : 03:20:35

quote:
Originally posted by sundarsrini_s

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 ON ds.DemandItemKey = di.ItemKey ON
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

but i get a error

Incorrect syntax near the keyword 'where'.

i also tried by using alias name.but it wont work

what can i do for this?

You're missing an alisa. Also you're using fields which are not returned from deriuved table. You need return di.ItemNumber,dbo.FS_LotTrace.LotNumber also from derived table to be used outside for grouping.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources