Tag Archives: sql

SAP Business One Queries

Batch information SQL query

Leave a comment

Here’s a SQL query that returns batch information for item codes that have a stock level greater than zero. This is the first in a series of posts highlighting some useful SQL queries for SAP Business One. I usually find myself writing out the same queries time & time again, so I thought I’d share them with the world. Some will be simple, some will be complex, hopefully all will be useful!

To keep things simple, wherever possible, I will not be using any User Defined Fields that I have created as part of my day-to-day work. However, sometimes they can be handy so if I include one, I’ll explain the reasoning why.

SELECT 
     T0.[ItemCode],
     T0.[DistNumber],
     T0.[LotNumber],
     T0.[Notes],
     T1.[WhsCode],
     T1.[Quantity],
     T1.[CommitQty],
     T2.[Location],
     T0.[Status],
     T3.[SWW]
 FROM
     [dbo].[OBTN] T0
     LEFT OUTER JOIN [dbo].[OBTQ] T1 ON T0.[ItemCode] = T1.[ItemCode] AND T0.[SysNumber] = T1.[SysNumber] 
     INNER JOIN [dbo].[OBTW] T2 ON T0.[ItemCode] = T2.[ItemCode] AND T0.[SysNumber] = T2.[SysNumber] AND T1.[WhsCode] = T2.[WhsCode]
     INNER JOIN [dbo].[OITM] T3 ON T0.[ItemCode] = T3.[ItemCode]
 WHERE 
     T1.[Quantity] > 0

Columns returned:

  • Item Code
  • Batch Number
  • Batch Attribute 2
  • Notes
  • Warehouse Code
  • Quantity
  • Committed Quantity
  • Location
  • Status
  • Item Additional Identifier

By a strange quirk of fate I had cause to use this query moment after I had written the first draft of this post!

You can find this query, and more over at my GitHub page: https://github.com/roguelj/SAP-Business-One-SQL-Queries

There are more SAP Business One queries on my blog, and you can find them here directly.

Coding

SQL Trim with Alternative Characters

SQL Trim UDF

.NET strings have a method that allow you to trim characters from the beginning and end of a string. There is also a SQL trim function, but you can only trim the space character, unfortunately you cannot specify which character you want to trim. I sought to rectify this with a UDF:

CREATE FUNCTION TrimSingleChar
(
-- Add the parameters for the function here
@needle nchar(1),
@haystack as nvarchar(max)
)
RETURNS nvarchar(max)
AS
BEGIN
DECLARE @pos as int -- variable to hold the position of @needle

-- Check whether @needle appears at the start of the string
SELECT @pos = CHARINDEX(@needle,@haystack,0)
IF @pos = 1
BEGIN
SELECT @haystack = SUBSTRING(@haystack,2,LEN(@haystack) -1)
END

-- Reverse the string
SELECT @haystack = REVERSE(@haystack)

-- Check again whether @needle appears at the start of the string
SELECT @pos = CHARINDEX(@needle,@haystack,0)
IF @pos = 1
BEGIN
SELECT @haystack = SUBSTRING(@haystack,2,LEN(@haystack) -1)
END

-- Reverse the string to back how it was, and return it
RETURN REVERSE(@haystack)

END
GO

Unfortuantely at the moment this function will only trim a single instance of @needle from the beginning and end, but at the moment that’s all I need.

As .NET has native functions available to perform the above task, a better option would be to use a SQL CLR function. That’s beyond the scope of this article, however. Maybe next time! For more information about SQL CLR functions, you can check out more at this link: http://msdn.microsoft.com/en-us/library/ms131077.aspx

For more information about SQL UDF functions, you can read more here: http://technet.microsoft.com/en-us/library/aa214363(v=sql.80).aspx