Null results ? - 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

SQL Server 2005 Forums

Transact-SQL (2005)

Null results ?

Author

Topic

direrayne
Starting Member

30 Posts

Posted - 2010-10-21 : 13:23:23

The Query bellow works however it should only return 1 row however it return 126 rows, the first row is correct however, all the others the last column, " [QTYON] " returns all null results i need to ajust the query to remove the extra rows. and i do not know how to do it.
Need some help please.

SELECT
--f.[DOCDATE]
--,[SaleTime]
--,[BACHNUMB]
--,f.[LOCNCODE]
f.[ItemLookupCode]
--,[ItemDescription]
,f.[TrxQuantity]
--,[TrxPrice]
--,[TrxCost]
,f.[TrxFullPrice]
,f.[TrxFullCost]
,f.[TrxGrossProfit]
,f.[Vendor]
--,[ITMSHNAM]
--,[ITMGEDSC]
,f.[ITEMDESC]
--,[ITMCLSCD]
,f.[Department]
--,[Category]
,f.[VNDITNUM]
--,[LSTORDDT]
--,[LSRCPTDT]
--,[QtyOnOrderSite]
--,[QtyOnHandSite]
,(Select i.[QTY On Hand] where i.[record type] = 'overall') as [QTYON]
FROM [ICL].[dbo].[ICL_Sockeye_Flashreport] f
inner join dbo.ItemQuantities i
on f.[itemlookupcode] = i.[item number]
where [itemlookupcode] = 'N4964W5-18-XX'

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2010-10-21 : 14:22:12

1. can you show us the result it brings back
2. how many rows do you get back when removing the

,(Select i.[QTY On Hand] where i.[record type] = 'overall') as [QTYON]

If you don't have the passion to help people, you have no passion

direrayne
Starting Member

30 Posts

Posted - 2010-10-21 : 14:49:18

this is the result. except the last line goes on for over a hundred rows and is an exact copy of the first 3 rows just with NULLs

without the last column just the 3 rows appear.

ItemLookupCode TrxQuantity TrxFullPrice TrxFullCost TrxGrossProfit Vendor ITEMDESC Department VNDITNUM QTY On Hand
N4964W5-18-XX 1 956 511.86 444.14 IJMM 14KW DIA BY THE YARD NECK 18" 0.75TW DIAM N4964W5-18-XX 8.00000
N4964W5-18-XX 1 896.25 511.86 384.39 IJMM 14KW DIA BY THE YARD NECK 18" 0.75TW DIAM N4964W5-18-XX 8.00000
N4964W5-18-XX 1 927.32 511.86 415.46 IJMM 14KW DIA BY THE YARD NECK 18" 0.75TW DIAM N4964W5-18-XX 8.00000
N4964W5-18-XX 1 956 511.86 444.14 IJMM 14KW DIA BY THE YARD NECK 18" 0.75TW DIAM N4964W5-18-XX NULL

direrayne
Starting Member

30 Posts

Posted - 2010-10-21 : 16:12:34

got it. i moved the sub query out into the main one

SELECT
--f.[DOCDATE]
--,[SaleTime]
--,[BACHNUMB]
--,f.[LOCNCODE]
f.[ItemLookupCode]
--,[ItemDescription]
,f.[TrxQuantity]
--,[TrxPrice]
--,[TrxCost]
,f.[TrxFullPrice]
,f.[TrxFullCost]
,f.[TrxGrossProfit]
,f.[Vendor]
--,[ITMSHNAM]
--,[ITMGEDSC]
,f.[ITEMDESC]
--,[ITMCLSCD]
,f.[Department]
--,[Category]
,f.[VNDITNUM]
--,[LSTORDDT]
--,[LSRCPTDT]
--,[QtyOnOrderSite]
--,[QtyOnHandSite]
,i.[QTY On Hand] --where i.[record type] = 'overall'

FROM [ICL].[dbo].[ICL_Sockeye_Flashreport] f
inner join dbo.ItemQuantities i
on f.[itemlookupcode] = i.[item number]
where [itemlookupcode] = 'N4964W5-18-XX' and i.[record type] = 'overall'

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources