References to functions

The position of the arguments is important and must conform to the function definition for the semantics to be correct. Both the position of the arguments and the function definition must conform to the function body.

Each reference to a function, whether it is a UDF, or a built-in function, contains the following syntax:

In the preceding syntax diagram, function_name can be either an unqualified or a qualified function name. The arguments can number from 0 to 90 and are expressions. Examples of some components that can compose expressions are the following:

a column name, qualified or unqualified
a constant
a host variable
a special register
a parameter marker

The database manager does not attempt to shuffle arguments to better match a function definition, and do not understand the semantics of the individual function parameters.

Use of column names in UDF argument expressions requires that the table references that contain the columns have proper scope. For table functions referenced in a join and using any argument involving columns from another table or table function, the referenced table or table function must precede the table function containing the reference in the FROM clause.

You cannot use the following code to specify the parameter markers in the BLOOP function:

BLOOP(?)

Because the function selection logic does not know what data types the argument might turn out to be, it cannot resolve the reference. You can use the CAST specification to provide a type for the parameter marker. For example, INTEGER, and then the function selection logic can proceed:

BLOOP(CAST(? AS INTEGER))

Some valid examples of function invocations are:

      AVG(FLOAT_COLUMN)
      BLOOP(COLUMN1)
      BLOOP(FLOAT_COLUMN + CAST(? AS INTEGER))
      BLOOP(:hostvar :indicvar)
      BRIAN.PARSE(CHAR_COLUMN CONCAT USER, 1, 0, 0, 1)
      CTR()
      FLOOR(FLOAT_COLUMN)
      PABLO.BLOOP(A+B)
      PABLO.BLOOP(:hostvar)
      "search_schema"(CURRENT FUNCTION PATH, 'GENE')
      SUBSTR(COLUMN2,8,3)
      SYSFUN.FLOOR(AVG(EMP.SALARY))
      SYSFUN.AVG(SYSFUN.FLOOR(EMP.SALARY))
      SYSIBM.SUBSTR(COLUMN2,11,LENGTH(COLUMN3))
      SQRT((SELECT SUM(length*length)
           FROM triangles
           WHERE id= 'J522'
           AND legtype <> 'HYP'))

If any of these functions are table functions, the syntax to reference them is slightly different than presented previously. For example, if PABLO.BLOOP is a table function, to properly reference it, use:

TABLE(PABLO.BLOOP(A+B)) AS Q