October 21, 2010 at 1:08 pm
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'
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.
ItemLookupCodeTrxQuantityTrxFullPriceTrxFullCostTrxGrossProfitVendorITEMDESCDepartmentVNDITNUMQTY On Hand
N4964W5-18-XX1956511.86444.14IJMM 14KW DIA BY THE YARD NECK 18" 0.75TW DIAMN4964W5-18-XX 8.00000
N4964W5-18-XX1896.25511.86384.39IJMM 14KW DIA BY THE YARD NECK 18" 0.75TW DIAMN4964W5-18-XX 8.00000
N4964W5-18-XX1927.32511.86415.46IJMM 14KW DIA BY THE YARD NECK 18" 0.75TW DIAMN4964W5-18-XX 8.00000
N4964W5-18-XX1956511.86444.14IJMM 14KW DIA BY THE YARD NECK 18" 0.75TW DIAMN4964W5-18-XX NULL
October 21, 2010 at 2:00 pm
First of all without the table structuresand some sample data it is impossible to give you a tested answer.
All I can suggest is that you use Books On Line (SQL Server help) and look at the use of the IS NOT NULL clause.
October 21, 2010 at 2:03 pm
Why are you using a subquery for the final column? Why not just move "where i.[record type] = 'overall'" into the Where clause of the main query?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
October 21, 2010 at 2:11 pm
GSquared (10/21/2010)
Why are you using a subquery for the final column? Why not just move "where i.[record type] = 'overall'" into the Where clause of the main query?
That was it.. it works perfectly like this
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'
Thank you very much. i have learnt something today.
October 22, 2010 at 6:57 am
You're welcome.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply