problem in select query

Question

Post reply

problem in select query

khurram sultan

SSC-Addicted

Points: 485
More actions
August 18, 2010 at 11:25 pm

#225973

The Following Query works fine to get stock from a ledger table of all items
but i don't want 0 stock items to be selected
i used where stock > 0
in the following query but it doesn't work any ideas what i m missing here
thanks
SELECT tb_final_item_Report_1.* ,
(select top 1 isnull(Stock, 0) from Tb_Item_Cost_Ledger where ItemId = tb_final_item_Report_1.CoA_ID and Vdate < = '20100101' Order By [Vdate] Desc, [VTYPE] Desc, [VMID] Desc, [OtherId] Desc, [VDID] Desc, [AutoId] Desc ) as Stock
FROM tb_final_item_Report_1
INNER JOIN Tb_Item
INNER JOIN Tb_UOM ON Tb_Item.UOM_Id = Tb_UOM.UomId ON tb_final_item_Report_1.CoA_ID = Tb_Item.COA_Id
INNER JOIN Vw_Final_Inventory_Report_1 ON Tb_Item.COA_Id = Vw_Final_Inventory_Report_1.CoA_ID

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 1

i don't think this is complete query as there is no stock > 0 clause , post complete query

.Additionally where are the table definition part ? that can help us more to help you.

see link in my signature

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

khurram sultan SSC-Addicted Points: 485 More actions · Answer 2

ok i will try to make it simple

there are 2 tables

1 tb_item

2 tb_item_cost_ledger

tb_item has all the items in it

tb_item_cost_ledger has running stock information in it with dates

the stock field is available in tb_item_cost_ledger with date

now i want to get the stock of all items on a specific date

so i used a sub query to get that stock

SELECT

Tb_item.Item_part_number ,

Tb_item.item_description,

(SELECT TOP 1 ISNULL(Stock, 0) FROM Tb_Item_Cost_Ledger WHERE (ItemId = Tb_Item.CoA_ID) AND (Vdate <= '20100101') ORDER BY Vdate DESC) as Stock

FROM Tb_Item

now i dnt want the zero stock items to show in the result

i hope that clears my question thx for response

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 3

SELECT TOP 1 Stock FROM Tb_Item_Cost_Ledger WHERE (ItemId = Tb_Item.CoA_ID) AND (Vdate <= '20100101')

AND ISNULL(stock, 0) > 0

ORDER BY Vdate DESC

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

khurram sultan SSC-Addicted Points: 485 More actions · Answer 4

i have tired that but it is still showing me items

any other ideas

drew.allen SSC Guru Points: 76996 More actions · Answer 5

khurram sultan (8/19/2010)
SELECT Tb_item.Item_part_number , Tb_item.item_description, (SELECT TOP 1 ISNULL(Stock, 0) FROM Tb_Item_Cost_Ledger WHERE (ItemId = Tb_Item.CoA_ID) AND (Vdate <= '20100101') ORDER BY Vdate DESC) as Stock FROM Tb_Item

The problem is with the scope of your ISNULL(). You're checking for NULL values in the subquery, when you probably want to check for NULL values in the main query.

SELECT

Tb_item.Item_part_number ,

Tb_item.item_description,

ISNULL( (

SELECT TOP 1 Stock

FROM Tb_Item_Cost_Ledger

WHERE (ItemId = Tb_Item.CoA_ID)

AND (Vdate <= '20100101')

ORDER BY Vdate DESC)

, 0) as Stock

FROM Tb_Item

The first one will only return a 0 if the column is null in the first row that meets the criteria. It will return a NULL if there are no rows that meet the criteria. The second one will return 0 in both cases.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA