pls provide the correct query for this!

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

pls provide the correct query for this!

Author

Topic

sundarsrini_s
Starting Member

6 Posts

Posted - 2008-06-02 : 10:00:29

hi!

select min(dbo.FS_ItemInventory.Bin) from
(SELECT MIN(dbo.FS_LotTrace.LotNumber),dbo.FS_ItemInventory.Bin 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,dbo.FS_ItemInventory.Bin)

when i tried this query,
i get a error like

Incorrect syntax near ')'

pls provide the correct query for this.

reg,
s.srini

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-02 : 10:06:39

There are lot of inconsisitencies. You've missed couple of ON conditions and also used ON for seperating multiple conditions instead of AND. Also you've given aliases for some while others you've left out. Also suggest to follow proper indentation.

select min(dbo.FS_ItemInventory.Bin) from
(
SELECT MIN(dbo.FS_LotTrace.LotNumber),
dbo.FS_ItemInventory.Bin 
FROM dbo.FS_LotTrace 
RIGHT OUTER JOIN dbo.FS_MOHeader AS h 
ON condition missing
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 
ON Condition Missing
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,dbo.FS_ItemInventory.Bin)tmp

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-06-02 : 10:11:46

Where is the JOIN information for the FS_LotTrace table?

select	min(dbo.FS_ItemInventory.Bin)
from	(
		SELECT		MIN(dbo.FS_LotTrace.LotNumber),
				dbo.FS_ItemInventory.Bin
		FROM		dbo.FS_LotTrace
		RIGHT 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 JOIN	dbo.FS_DemandSupply AS ds ON l.MOLineKey = ds.TopLevelDemandSupplyKey
		LEFT JOIN	dbo.FS_ItemInventoryon ON dbo.FS_LotTrace.LotTraceKey = dbo.FS_ItemInventory.LotTraceKey 
		RIGHT JOIN	dbo.FS_Item AS di ON dbo.FS_ItemInventory.ItemKey = di.ItemKey 
					AND on ds.DemandItemKey = di.ItemKey
		where		dbo.FS_ItemInventory.InventoryCategory!='H'
				or dbo.FS_ItemInventory.InventoryCategory is null
		group by	di.ItemNumber,
				dbo.FS_ItemInventory.Bin
	) AS d

E 12°55'05.25"
N 56°04'39.16"

sundarsrini_s
Starting Member

6 Posts

Posted - 2008-06-03 : 01:19:39

select min(dbo.FS_ItemInventory.Bin) from
(
SELECT MIN(dbo.FS_LotTrace.LotNumber),
dbo.FS_ItemInventory.Bin
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,dbo.FS_ItemInventory.Bin)tmp

i also tried this.but still i am getting error like

Msg 8155, Level 16, State 2, Line 1
No column was specified for column 1 of 'tmp'.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "dbo.FS_ItemInventory.Bin" could not be bound.

so pls reply me.its utgent

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources