COALESCE function
Functions
COALESCE{ITEM_LIST}
If the data processed by the function is the null character, searches the field specified by ITEM_LIST from the left and returns the first non-null character.
If the data processed by the function is not the null character, returns the data processed by the function.
Parameters
ITEM_LIST
Specify the items to be searched for a value.
An array can be used to specify multiple items. (You can specify a maximum of 255 items.)
The search begins with the mapped item and then the item specified leftmost, followed by a sequential search based on the items to the right of the leftmost item.
If the specified items are all without values, null is returned. To return a value other than null, specify the character you want to return at the far right of ITEM_LIST.
Basically, when you use one of the reserved symbols, comma (,), left curly bracket ({), right curly bracket (}), or slash (/), in an argument of a function, insert a backslash (\) as the escape character.
However, if either of the following conditions is met, do not insert an escape character even when a comma (,), left curly bracket ({), or right curly bracket (}) is contained in the argument. In such a case, if you use the escape character, data processing is not performed correctly. (Note that, in the case of a slash (/), the escape character is necessary.)
-
An argument is specified to contain a variable [$DSTRXX] that is dynamically substituted with a value
-
An argument of common components is specified
Example
1. Replacing the third record of the mapped item with !
Figure A.4 Example of using COALESCE function (1)
2. Search in the order: Mapped item No.1 -> Item No.2 -> !
Figure A.5 Example of using COALESCE function (2)