As the documentation for the ODBC driver states scalar functions can be divided into three sections, Strings, Numbers, and Date/Time.
I will go through a real example of the scalar functions using a Sage 100 2013 ProvideX database.

Scalar functions go in the following format within the SQL statement { fn function_here }

The following example uses a scalar function that finds ASCII code characters.
Here I am using the function to find all Sage 100 customers where a name field has a space character. It’s possible to use this kind of function to clean up data such as find all rows of customer names where the customer name has special charters that could impact data import export to desperate systems.

SELECT TOP 2 A.CustomerName, {fn ascii(A.CustomerName)} AS ASCII_CODE
FROM AR_Customer a 
WHERE ({fn ascii(A.CustomerName)} = 32)

When using Sage 100 with the ProvideX database ODBC scalar functions can be useful in our SQL statements for date conversions, data check, and data manipulation. Below are links for the ProvideX scalar functions and another link for information about ODBC scalar functions. They can be used in scripts and other Sage 100 functions that use the Sage 100 ODBC driver.


Commenting is closed for this article.