Apply Engine Functions facilitate development by encapsulating sets of instructions that perform a variety of tasks including the direct manipulation and transformation of data, data analysis and runtime process control. Functions must be used within an SQData SELECT command. Functions can be used in singular mode or they can be nested with other functions, allowing for advanced processing capability.
This section of the Engine Reference provides both guidelines for using Functions and detailed examples of solutions to common problems encountered when developing data replication and transformation Engine scripts.
Guidelines
SQData functions must adhere to the following guidelines:
- Functions may be used only within an SQData SELECT command.
- Parameter values of functions are specified between opening and closing parenthesis and can be represented by one or more of the following:
- A field/column within a source datastore.
- A literal value enclosed in single quotes (i.e.'MUSTANG').
- The result/output of another SQData function.
- Nothing - Functions that do not require a parameter value do require the open close parenthesis ().
- Mathematical functions require that all values be numeric.
- Functions can be nested.
- When using nested functions, it is helpful to put the ending parenthesis for the "outer" function on a separate line and place the "inner" functions within the starting and ending parenthesis.
- Functions are processed internally as a "stack" within the SQData Apply Engine and must be coded as such with the script. For example, the expression:
if (TEMPERATURE = 'HOT' and HUMIDITY = 'HIGH')
{
V_CONDITION = 'MUGGY'
}- Boolean functions return a true (1) or false (0) condition. If the result of a Boolean function is true, then any functions specified within the Boolean function are executed. Otherwise, the functions are not executed.
- Certain data string functions, such as C2X and X2C, will translate the data string into the code page in which the operation takes place. For example, if a translation is performed on a z/OS platform, the result of a function will be in EBCDIC format. If the translation is performed on a UNIX platform, the result will be in ASCII format.
Syntax
SQData functions, including nested function syntax, and provides examples of both nested (function set) and simple (standalone) functions.
function_name (parameter [, parameter]) [function_alias]| Keyword | Description |
|---|---|
| function_name |
The name of the SQData function. May be specified in either upper or lower case |
| parameter |
One or more arguments required by the function. Refer to the specific function for a description of the required and/or optional arguments required by that function. Parameters can also be another function. Nested functions are described in more detail below. |
| function_alias | Specifies a name/tag that can be assigned to a particular function instance. A function alias is particularly useful for re-using a function set (i.e. nested functions) such as for common calculations. In this case, you would use the VALUE function to reference the function alias. |
Example 1
TRANSLATE (‘MUSTANG’, ‘A’, ‘X’)
,SUM (ANNUAL_SALARY)Example 2
TRANSLATE (‘MUSTANG’, ‘A’, ‘X’) TRANS_FN
,SUM (ANNUAL_SALARY) SUM_FN