Can't understand why some data is repeated
-
Hi Everyone,
I am trying to eliminate repeated data in my results, towards which end I am using the ROW_NUMBER() function, however I cannot understand why in some cases I am getting repeats.
Here is my code:
/*
**Date:8/04/2014
**Issue:Double ups under Bin Label and Bin Qty
**Other:Adding in the Warehouse table OITW massively increases data 'repeats'
**Resolution:Unknown
*/
go
declare @whCode varchar(5), @binLoc varchar(2)
set @whCode = '04'
if @whCode = ''
set @whCode = '%'
if @whCode = '02'
set @binLoc = 'S%'
else
if @whCode = '03'
set @binLoc = 'M%'
else
if @whCode = '04'
set @binLoc = 'B%'
else
if @whCode = '%'
set @binLoc = '%'
;
with CTEResultSet
as
(
select ROW_NUMBER()
over
(
partition by td.itemCode
order by td.itemCode
) as Seq,
td.ItemCode as ItemCode
, td.Dscription as ItemDescription
, cast(td.Quantity as decimal(10,0)) as OrderQty
--, cast(twhs.OnHand as decimal(10,0)) as OnHand
, th.CardCode as BPCode
, th.CardName as BPName
, cast(th.DocDate as nvarchar(20)) as DocumentDate
, cast(th.DocDueDate as nvarchar(20)) as DocDueDate
, cast(th.DocNum as decimal (10,0)) as SONumber
, tsp.SlpName as SPerson
--, twhs.WhsCode as WhsCode
, isnull(tbloc.BINLABEL, '') as BLabel
, cast(tbloc.QUANTITY as decimal(10,0)) as BQty
from AU.dbo.RDR1 td
left outer join AU.dbo.ORDR th on th.DocEntry = td.DocEntry
--left outer join AU.dbo.OITW twhs on twhs.ItemCode = td.ItemCode
left outer join AU.dbo.OSLP tsp on tsp.SlpCode = td.SlpCode
left outer join A1Warehouse.dbo.BINLOCAT tbloc on tbloc.PRODUCT = td.ItemCode collate SQL_Latin1_General_CP850_CI_AS
where
td.LineStatus = 'O'-- Order is Open
--and twhs.WhsCode like @whCode-- Only stock from Whs Code
and td.WhsCode like @whCode-- Order lines are from Whs Code
and tbloc.QUANTITY > 0-- Location has Qty > 0
and tbloc.BINLABEL like @binLoc-- BinLabel corresponds with Warehouse
)
select
Seq,
case when Seq = 1 then ItemCode else '' end as 'Item Code'
, case when Seq = 1 then ItemDescription else '' end as 'Item Description'
, case when Seq = 1 then cast(OrderQty as nvarchar(20)) else '' end as 'Order Qty'
--, case when Seq = 1 then cast(OnHand as nvarchar(20)) else '' end as 'On Hand'
, case when Seq = 1 then BPCode else '' end as 'BP Code'
, case when Seq = 1 then BPName else '' end as 'BP Name'
, case when Seq = 1 then cast(SONumber as nvarchar(20)) else '' end as 'Sales Order No.'
, case when Seq = 1 then SPerson else '' end as 'Sales Person'
--, case when Seq = 1 then WhsCode else '' end as 'Whs Code'
, BLabel as 'Bin Label'
, isnull(cast(BQty as nvarchar(20)), '') as 'Bin Qty'
from CTEResultSet
order by ItemCode, BLabel
Here is a screen capture (sample) of my data:
Any help will be greatly appreciated.
Kind Regards,
Davo
-
If you want to know why it's happening, stop blanking out fields so you can see what item code and order number are being repeated, then look at those entries in each of the source tables to see why one of them has more than one row. Then you can decide what to do about it.
You're not doing any grouping. You haven't excluded any rows with a where clause. You're joining multiple tables and the issue is obviously in the data.
PS: I find it hilarious that there are spelling errors in the table/column names and that nobody ever fixed them. Dscription? Sweet.
-
You're getting duplicates because you're only looking at ItemCode...
select ROW_NUMBER()over
(
partition by td.itemCode
order by td.itemCode
) as Seq, You might want to partition on [Bin Label] as well (depends on what you're really after). If you only care about the ItemCode, remove all that CASE mumbo jumbo and simply add the code below to your final SELECT from the CTE:
WHERE Seq = 1
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
-
Thanks for your kind responses. I have now extracted the data that I require from my tables, and almost as importantly achieved my desired visual layout with T-SQL alone!
-
Did the suggestion help or did you go another route?
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
-
Yes. I did need to change the partition by and order by clause; however the biggest improvement to my results came by using the AND clause on two of my tables, so instead of joining on one table attribute I performed a join only when two attributes were equal. Ultimately I needed to retain the case statement in order to get the desired visual layout.
-
Just curious - what do you see when you have multiple open sales orders for the same item?
Is it what you expect?
-
Hi Greg,
Yes results are as I had hoped, however I needed to change my code quite significantly. The major improvement came through adding and AND clause to two of my table joins, please see the new code below -
go
declare @whsNo varchar(3)
set @whsNo = '03'
if @whsNo = ''
set @whsNo = '%'
with CTEResultSet
as
(
select ROW_NUMBER()
over(partition by td.ItemCode, th.DocNum
order by td.ItemCode, th.DocNum, tbin.BINLABEL)
as RowsReturned
, td.ItemCode as 'Item Code (RDR1)'
, td.Dscription as 'Item Description (RDR1)'
, cast(th.DocNum as nvarchar(10)) as 'Sales Order Number (ORDR)'
, cast(td.Quantity as decimal(10,0)) as 'SO Qty'
, th.CardCode as 'BP Code'
, th.CardName as 'BP Name'
, cast(cast(th.DocDueDate as date) as nvarchar(20)) as 'Sales Order Due Date (ORDR)'
, td.WhsCode as 'Warehouse Code (RDR1)'
, cast(twhs.OnHand as decimal(10,0)) as 'Qty on Hand (OITW)'
, isnull(tbin.BINLABEL, '') as 'Bin Label (BINLOCAT)'
, cast(tbin.QUANTITY as decimal(10,0)) as 'Qty in Bin (BINLOCAT)'
, cast(tbin.PACKSIZE as decimal(10,0)) as 'Packsize (BINLOCAT)'
from AU.dbo.RDR1 td
inner join AU.dbo.ORDR th on th.DocEntry = td.DocEntry
left outer join AU.dbo.OITW twhs on twhs.ItemCode = td.ItemCode and twhs.WhsCode = td.WhsCode
left outer join A1Warehouse.dbo.BINLOCAT tbin on tbin.PRODUCT = td.ItemCode collate SQL_Latin1_General_CP850_CI_AS and tbin.WAREHOUSE = td.WhsCode collate SQL_Latin1_General_CP850_CI_AS
where td.WhsCode like @whsNo and td.LineStatus = 'O'
)
select
--[RowsReturned] as 'Row Number'
case when RowsReturned = 1 then [Item Code (RDR1)] else '' end as 'Item Code'
, case when RowsReturned = 1 then [Item Description (RDR1)] else '' end as 'Item Description'
, case when RowsReturned = 1 then [Sales Order Number (ORDR)] else '' end as 'Sales Order No.'
, case when RowsReturned = 1 then [Sales Order Due Date (ORDR)] else '' end as 'Due Date'
, case when RowsReturned = 1 then cast([SO Qty] as nvarchar(20)) else '' end as 'SO Qty'
, case when RowsReturned = 1 then [BP Code] else '' end as 'BP Code'
, case when RowsReturned = 1 then [BP Name] else '' end as 'Business Partner Name'
, case when RowsReturned = 1 then [Warehouse Code (RDR1)] else '' end as 'Whs Code'
, case when RowsReturned = 1 then cast([Qty on Hand (OITW)] as nvarchar(20)) else '' end as 'Qty on Hand (B1)'
, [Bin Label (BINLOCAT)] as 'Bin Label (A1)'
, isnull(cast([Qty in Bin (BINLOCAT)] as nvarchar(20)), '') as 'Qty in Bin (A1)'
, isnull(cast([Packsize (BINLOCAT)] as nvarchar(20)), '') as 'Packsize (A1)'
from CTEResultSet
Ironically when I 'plugged' this code into SAP Business One (with slight variations to the variable declaration) it would not run. The jury is out on the exact reason why, however Business One does not support Stored Procedures, and this is the most likely cause (not sure if Boyum, the usability add-on that is installed with Business One will allow Stored Procedures!, hence why the jury is out!).
Kind Regards,
David
-
I'd say that partitioning and order by helped just as much. 🙂
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply