Results 1 to 2 of 2
  1. #1
    Join Date
    Mar 2014
    Posts
    35

    Unanswered: Results doubling up in two columns, can't work out why?

    Hi Everyone,

    I have written some SQL that relates to the (partial) result set below -

    http://i59.tinypic.com/ir14xk.jpg

    I am having an incredibly difficult time trying to work out why I am seeing double ups in the 'Bin Label' and 'Bin Qty' columns.

    My query looks like the following -

    Code:
    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
    	inner join AU.dbo.ORDR th on th.DocEntry = td.DocEntry
    	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 --AND tbloc.WAREHOUSE = td.WhsCode collate SQL_Latin1_General_CP850_CI_AS
    
    	where 
    	td.LineStatus = 'O'					-- Order is Open
    	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 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, Seq, BLabel
    If anybody can shed some light on why my results are doubling up in the two indicated columns it will be greatly appreciated.

    Kind Regards,

    David

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    My guess was that two th(or two tsp) might be joined for a td.

    Please try by removing th(or tsp), then see the duplicated ouput were disappeared?

    like...
    Code:
    ...
    ...
    
    	from AU.dbo.RDR1 td
    /*
    	inner join AU.dbo.ORDR th on th.DocEntry = td.DocEntry
    */
    	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 --AND tbloc.WAREHOUSE = td.WhsCode collate SQL_Latin1_General_CP850_CI_AS
    
    ...
    or
    Code:
    ...
    ...
    
    	from AU.dbo.RDR1 td
    	inner join AU.dbo.ORDR th on th.DocEntry = td.DocEntry
    /*
    	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 --AND tbloc.WAREHOUSE = td.WhsCode collate SQL_Latin1_General_CP850_CI_AS
    
    ...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •