Mastering SQL Queries for SAP Business One

General FMS query examples

Other than the auto code FMS query, the other types will be difficult to classify. The name general is chosen for all other queries. To be able to create a good FMS query, a good practice is to try more whenever you have the opportunity. The examples covered in this chapter will give you a solid foundation to start building your own FMS query.

Case 1—Double quotes should be avoided

A user created a very simple query. It gave an error message when assigned to FMS. The query is as follows:

IF (2500 < $[ORDR.DocTotal.number] or $[ORDR.CardCode] like "C99%")
SELECT 'Pre Pay No Charge'
ELSE
SELECT 'Pre Pay and Bill'

The logic is so simple that it should be correct. Where is the problem? Here is the solution:

IF (2500 < $[ORDR.DocTotal.number] ...