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

    Unanswered: Visual formatting of data returned from a SAP database

    Hi Everyone,

    I am working with extracting data from a SAP database, and I would like to perform some 'visual formatting' on the returned data. Because the SQL code that I am writing will be used in a custom Report it is not possible for me to use '3rd Party' formatting tools. Furthermore because the database is hosted on an MS SQL 2008 R2 RDBMS it is not possible to use the IIF() function.

    Here is a sample of the data that I am getting back at the moment -

    https://flic.kr/p/mH6BDd

    Here is how I would like the data to be returned (with the exception that the blue lines will ideally be blank cells) -

    https://flic.kr/p/mH6BLC

    I have come to believe that I should be using the ROW_NUMBER() function, along with the OVER() function and possibly the 'partition by' keywords.

    Here is my original code -
    Code:
    select
    td.ItemCode as 'Item Code'
    , td.Dscription as 'Item Description', td.Quantity as 'Order Qty'
    , titm.OnOrder as 'PO Qty'
    , th.CardCode as 'BP Code', th.CardName as 'BP Name'
    , th.DocNum as 'Sales Order Number'
    , tsp.SlpName as 'Sales Person'
    , twhs.WhsCode as 'Whs Code'
    , isnull(tbloc.BINLABEL, '') as 'BIN Label', isnull(cast(tbloc.QUANTITY as nvarchar(20)), '') as 'BIN Qty'
    
    from AU.dbo.RDR1 td
    left outer join AU.dbo.ORDR th on th.DocEntry = td.DocEntry
    left outer join AU.dbo.OITM titm on titm.ItemCode = td.ItemCode
    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'  and td.ItemCode = 'DR1124' and twhs.WhsCode like @whCode
    
    order by td.ItemCode, th.DocNum
    I am now trying to incorporate the previously mentioned functions with limited success.

    https://flic.kr/p/mH818j

    Notice above that I only see the 'Seq' and no other data. How can I go about modifying this code so that I see all of the data columns I desire?

    If I further 'refine' the above, by adding case statements and the individual columns that I want to see (as opposed to using the * wildcard) I get a long list of error messages.

    https://flic.kr/p/mH81cN

    I am very new to MS SQL programming so I figure that I have probably made some 'largish' errors above. If anybody can help me correct my code (the longer the explanation the better) it will be much appreciated.

    Kind Regards,

    Davo
    Last edited by Darts75; 04-03-14 at 01:34.

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Your problem is easy to solve, but I need to have a better UI than a tablet to answer. I'll do so in about twelve hours if nobody beats me to it.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Mar 2014
    Posts
    35
    That would be amazing, thank you.

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Your last screenshot is very close indeed.
    All you need to do is remove the aliases on your outermost query!
    George
    Home | Blog

  5. #5
    Join Date
    Mar 2014
    Posts
    35

    Now receiving an error message regarding the ) bracket

    Hi gvee,

    Thank you for your response. I have removed the alias from my outermost query however I am now receiving the message -


    Msg 102, Level 15, State 1, Line 39
    Incorrect syntax near ')'.
    This relates to the closing bracket at the bottom of my code.

    Any further advice will be greatly appreciated.

    Kind Regards,

    David
    Last edited by Darts75; 04-03-14 at 21:23. Reason: spacing

  6. #6
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Apologies: you still need to alias the subquery!

    Add your " As ResultsGroup" back on.
    George
    Home | Blog

  7. #7
    Join Date
    Mar 2014
    Posts
    35

    Is what I am trying to do possible with SQL, give me Excel!!!

    Hi Everyone,

    I have had a degree of success with the code below - however I am still seeing some double ups (triple ups, etc) in my data. I am at a loss to explain where the repetition is creeping in, and any further advice will be greatly appreciated.
    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 small screen cap of the data repetition issue that I am still facing -
    Screen Cap

    Why?Why?Why?Why?...going CRAZY!!!

    Thanks

    Davo

  8. #8
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Note: Just because you CAN do something that doesn't mean that you SHOULD do it! Formatting like group indication ought to be done on the client, not on the database or the application servers.

    With that said (and stressed), I'm pretty sure thatyou can do this using:
    Code:
    SELECT
       CASE WHEN 1 = zzRowNumber THEN z.[Item Code] END AS 'Item Code'
    ,  CASE WHEN 1 = zzRowNumber THEN z.[Item Description] END AS 'Item Description'
    ,  CASE WHEN 1 = zzRowNumber THEN z.[Order Qty] END AS 'Order Qty'
    ,  z.[PO Qty]
    ,  z.[BP Code], z.[BP Name]
    ,  z.[Sales Order Number]
    ,  z.[Sales Person]
    ,  z.[Whs Code]
    ,  z.[BIN Label], z.[BIN Qty]
    
    FROM (
    select
    td.ItemCode as 'Item Code'
    , td.Dscription as 'Item Description', td.Quantity as 'Order Qty'
    , titm.OnOrder as 'PO Qty'
    , th.CardCode as 'BP Code', th.CardName as 'BP Name'
    , th.DocNum as 'Sales Order Number'
    , tsp.SlpName as 'Sales Person'
    , twhs.WhsCode as 'Whs Code'
    , isnull(tbloc.BINLABEL, '') as 'BIN Label', isnull(cast(tbloc.QUANTITY as nvarchar(20)), '') as 'BIN Qty'
    ,  Row_Number() OVER (PARTITION BY td.ItemCode ORDER BY th.DocNum) AS zzRowNumber
    from AU.dbo.RDR1 td
    left outer join AU.dbo.ORDR th on th.DocEntry = td.DocEntry
    left outer join AU.dbo.OITM titm on titm.ItemCode = td.ItemCode
    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'  and td.ItemCode = 'DR1124' and twhs.WhsCode like @whCode
    ) AS z
       ORDER BY td.ItemCode, th.DocNum
    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  9. #9
    Join Date
    Mar 2014
    Posts
    35
    Hi Pat,

    Thank you for your valuable input. I have managed to extract the data that I desire, and to visually format it the way that I want. I needed to perform visual formatting in T-SQL because the SQL that I wrote is for use with SAP Business One (as a custom report), and Business One does not accept '3rd party' formatting tools.

    Kind Regards,

    Davo

  10. #10
    Join Date
    Mar 2014
    Posts
    35
    Hi Pat,

    I am back to the drawing board, and will be using the code that you kindly supplied as a 'template' for future Business One work. It turns out that SAP Business One does not supported Stored Procedures!

    Kind Regards,

    Davo

Posting Permissions

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