If a function is called without im_filter value supplied, the following events occur:

• The DECLARE lv_filter commands result in an SQL error.
• The exit handler catches the error and runs the two commands in the innermost BEGIN…END pair. The value of lv_filter is set to an empty string and lt_official populated from the APPLY_FILTER expression. The line between the two END statements is skipped.

If the lv_filter declaration is valid, the scripts skip to the line after innermost END. In every case, the scripts perform the RETURN statement, which is now guaranteed to have a valid value of lt_official to draw on.

Arrays in SQL Script

SQL script supports the definition of an arrays as an indexed collection of elements of a single data type.

With arrays in SQL script, you can do the following:

• Set and access elements by index using <array>[<index>]
• Add and remove elements from the ends of an array.
• Concatenate two arrays using CONCAT or ||
• Convert arrays into a table using UNNSET
• Convert a table column into an array using ARRAY_AGG
• Determine the length of the array using CARDINALITY

The syntax performs the following tasks:

• The UNNEST function converts the referenced arrays into ab table, with specified column names.
• In this case, the unit and factor arrays become columns called Unit and Factor in a table called lt_conversion.
• The COALESCE function returns the first non-null value from its input arguments.
• In this case, the im_to parameter is searched in the lt_conversion table and the appropriate value of Factor is returned
• If the :im_to value is invalid, no rows are returned and Factor is null.
• If no rows are returned and Factor is null, COALESCE skips the value of Factor and returns a conversion ratio of 1.0.