Hidden Fact About SAP Business One Query Generator That Will Make Your Life Easier

[anmol_gupta95]

 



SAP Business One (SAP B1) Query Generator helps a user to develop SQL Queries on the fly very easily. You have two major options to generate a query here -

1.  You can either select the required tables and conditions in the generator to build the required query.


2. You can build the query in the SQL Server Management Studio (SSMS) on the concerned SAP B1 company database directly, and paste the same into the query generator’s output-query window.

The second case allows you to develop complex queries or it may be easy if you are comfortable with SSMS already.

But in the 2nd case there is a catch - 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.

Document number shows Invoices and Credit Notes as in the below screenshot -



Overall, the result looks the same in both the SSMS and the Query Generator’s output, but the behavior of the document-links in the Query Generator’s output will be erratic. Say for e.g. if you have written a query of invoices and their linked corresponding credit-notes in a complex query where a union operator has been used then the document-links to the corresponding credit-notes will take you to the wrong documents (documents which have no links to their corresponding listed parent documents) from the Query Generator’s result windows.



AR Invoice with the same number as the credit Memo:

Code for Invoice Memo and Credit Memo:

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%%'

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%%'

SAP Business One / SAP B1 has earned the top spot for Small/Medium-sized Enterprises (SME) in recent times. The productivity and efficiency it offers is unparalleled among all the ERPs in the market within the given segment. It is one simple yet highly capable software incapacitating the requirements of any SME. Now, you can easily integrate SAP Business One with eCommerce, Marketplace, CRM, Shipping and POS systems to automate the business process with the help of APPSeCONNECT.