Working with SQL query generator variables

[Former Member]

Hi everyone,

I'd like to share with you some alternate ways you can use the Query Generator variables.

A big thank you to Timothy Wong who taught me this.

Also, after writing this document, I did happen to find an SAP note describing this syntax and purpose.
http://service.sap.com/sap/support/notes/730960
Anyhow, hopefully this guide below can help people without access to customer portal, and/or explain in more detail.

This was configured on a Windows 7 SP1 64-bit machine, running SAP Business One 32-bit Version 9.2 PL00 and Microsoft SQL Server 2014.

All software was on a single computer.

This document assumes some basic SAP B1 and SQL knowledge.
This is suited for Microsoft SQL Server database, but may work on SAP HANA with some tweaks.

When we work with variables using the Query Generator, we tend to run into problems when:

• We want to rename the variable description

• We use variables inside a sub query and although it might work in SSMS, it returns error in the SAP B1 Client Application
  Or alternatively, we use variables in some other way and the SAP B1 Client Application gives unusual error
  

• Variables do not work when the SQL statement includes non SAP tables
  A good example of this would be tables created by third party add-ons

To deal with these issues, we can write the SQL statement using special syntax for the variables.

For example:

 

We want to rename the variable description



Let's look at the special syntax used to declare and set the variables.

 

/**SELECT FROM [OFPR] T0 **/

DECLARE @DocDateFrom as datetime

/* WHERE */

Set @DocDateFrom = /* T0.F_RefDate */ '[%0]'

 

First thing to mention is that, I don't think it is possible to rename the variable description to anything.

However, using the above syntax, we can choose an existing description that is most suitable for our purpose.

 

/**SELECT FROM [OFPR] T0 **/

DECLARE @DocDateFrom as datetime

/* WHERE */

Set @DocDateFrom = /* T0.F_RefDate */ '[%0]'

 

See the first line in bold.
I have chosen the OFPR table which holds the Posting Period information.
Then in the fourth line, I have chosen the F_RefDate field.

 

Example below.



Now when the query gets executed, it knows to grab the description from here.
You may notice that the List of existing values box is now pulling all available data from the OFPR, F_RefDate field.



This doesn't mean you have to choose a value from here.
As it's a variable, you can freely type in whatever value you like.

Have a try yourself with the full query below:

 

/**SELECT FROM [OFPR] T0 **/

DECLARE @DocDateFrom as datetime

/* WHERE */

Set @DocDateFrom = /* T0.F_RefDate */ '[%0]'

 

/**SELECT FROM [OFPR] T0 **/

DECLARE @DocDateTo as datetime

/* WHERE */

Set @DocDateTo = /* T0.T_RefDate */ '[%1]'

 

SELECT *

FROM ORDR T0

WHERE T0.DocDate >= @DocDateFrom AND T0.DocDate <= @DocDateTo

 

We use variables inside a sub query and although it might work in SSMS, it returns error in the SAP B1 Client Application.
Or alternatively, we use variables in some other way and the SAP B1 Client Application gives unusual error.

 

There is not too much else to explain with this, as it has been explained in the first example.

Here is the revised SQL statement.

 

/**SELECT FROM [OFPR] T0 **/

DECLARE @DocDateFrom as datetime

/* WHERE */

Set @DocDateFrom = /* T0.F_RefDate */ '[%0]'

 

/**SELECT FROM [OFPR] T0 **/

DECLARE @DocDateTo as datetime

/* WHERE */

Set @DocDateTo = /* T0.T_RefDate */ '[%1]'

 

SELECT A.CardCode, SUM(A.DocTotal) AS 'DocTotal'

FROM

(

     SELECT T0.CardCode, SUM(T0.DocTotal) AS 'DocTotal'

     FROM ORDR T0

     WHERE T0.DocDate >= @DocDateFrom  AND T0.DocDate <= @DocDateTo

     GROUP BY T0.CardCode

     UNION ALL

     SELECT T0.CardCode, SUM(T0.DocTotal)

     FROM ODLN T0

     WHERE T0.DocDate >= @DocDateFrom  AND T0.DocDate <= @DocDateTo

     GROUP BY T0.CardCode

) AS A

GROUP BY A.CardCode

 

Variables do not work when the SQL statement includes non SAP tables
A good example of this would be tables created by third party add-ons


 

Again, this has been explained in the first example.

Here is the revised SQL statement. I have included in the attachments a script to make this test table with some data. Use at own risk.

 

/**SELECT FROM [OHEM] T0 **/

DECLARE @EmpID as int

/* WHERE */

Set @EmpID = /* T0.empID */ '[%0]'

 

SELECT *

FROM TestTable

WHERE ID = @EmpID

 

This concludes the document.

I hope it helps you, and please ask any questions and/or provide feedback.

Kind Regards,

Nick Lakasas