SAP Business One Query Generator helps the user to develop SQL Queries on the fly very easily. Then the result/output of the queries can be analyzed and used for business within the SAP Business One itself. You have two major options to generate a query as follows as you know –
- You can either select the required tables and conditions in the generator to build the required query, or
- You can build the query in the SQL Server Management Studio (SSMS) on the concerned SAP Business One company database directly, and then copy-paste the same into the query generator’s output-query window.
It is obvious that the second case allows you to develop complex queries or it may be easy if you are comfortable with SSMS already, and you are well aware of the SAP Business One company database tables beforehand.
But in the 2nd case, there is a catch that you may create any complex query and that may be giving you the right result in the SSMS, but it may not be behaving properly in Query Generator’s output. It happens especially in the case of union operation in the query in SAP Business One versions of 8.x family or earlier.
For instance, the following query will show the Invoices in the beginning (1st part of the UNION clause) and Credit Notes (2nd part of the UNION clause) at the end.
The tables used in the following example are as follows-
In the 1st part of the UNION[code]
OINV -> A/R Invoice
INV1 -> A/R Invoice – Rows
INV12 -> A/R Invoice – Tax Extension
[/code]
In the 2nd part of the UNION[code]
ORIN -> A/R Credit Memo
RIN1 -> A/R Credit Memo – Rows [/code]
[code]SELECT T0.[DocNum], T0.[DocDate], T0.[DocStatus], T0.[CardCode], T0.[CardName], T0.[NumAtCard], T0.[Address2], T1.[LineNum], T1.[ItemCode], T1.[Dscription], T1.[Quantity], T1.[Price], T1.[LineTotal], T2.[CityS], T2.[StateS], T2.[ZipCodeS], T2.[CountryS]
FROM OINV T0 INNER JOIN INV1 T1 ON T0.DocEntry = T1.DocEntry INNER JOIN INV12 T2 ON T0.DocEntry = T2.DocEntry
WHERE T0.[DocDate] >= ‘20171001’ and T0.[DocDate] < ‘20180101’ and T1.[ItemCode] Like ‘%%90700%%'[/code]
[code]UNION ALL
SELECT T0.[DocNum], T0.[DocDate], T0.[DocStatus], T0.[CardCode], T0.[CardName], T0.[NumAtCard], T0.[Address2], T1.[LineNum], T1.[ItemCode], T1.[Dscription], T1.[Quantity], T1.[Price], T1.[LineTotal], null AS CityS, null AS StateS, null AS ZipCodeS, null AS CountryS
FROM ORIN T0 INNER JOIN RIN1 T1 ON T0.DocEntry = T1.DocEntry
WHERE T0.[DocDate] >= ‘20140101’ and T0.[DocDate] < ‘20150101’ and T1.[ItemCode] Like ‘%%90700%%'[/code]
Note – In the above query, you may need to adjust the WHERE clause arguments as per your environment to test it. Or it may not be required at all for a small number of records in the database.
Once you copy-paste and run the above-said query, the result/output in the Query Generator will look like as follows –
Overall the result looks same both in the SSMS and the Query Generator’s output, but the behavior of the document-links in the Query Generator’s output will be erratic. In the example query-above if you have listed the invoices on top of the list and the credit-notes are listed just after the invoices, then the document-links (in the first column) to the corresponding credit-notes (below the invoices, i.e. at the end of the list) will take you to the wrong documents from the Query Generator’s result windows.
Actually, it will take you to the invoices instead of the desired credit-notes corresponding to the document number displayed. Observe meticulously, the document number will be the same, but the document type will be wrong. Ideally, it had to take you to the Credit Notes corresponding to the document numbers displayed at the end. This has been indicated in the following figures as well.
AR Invoice with the same number of the credit memo:
Now, if you reverse the sides of the UNION clause, means, if you keep the credit-note part above and the invoice part below then all the rows will take you to the credit-notes irrespective of their actual document type.
Hence it is better to avoid this kind of queries in the query generator of SAP Business One versions before 9.x. However, in the latest versions, this has been resolved. So if you are using an older version of SAP Business One then you may consider upgrading your SAP Business One to the latest version to avoid many such loopholes in the older versions of the most popular ERP across the globe.
Now, you can easily integrate your SAP Business One ERP with Ecommerce store, Marketplace, CRM, Shipping and POS Systems to automate the business process!
You may also like:
Indirect Access For SAP Business One – Updates and Changes
SAP Business One 9.3 is Available on the SAP Cloud Appliance Library
Advantages of SAP Business One OnDemand
