Your SQL career success depends on how best you can use built-in functions. Explained the key differences for Table, Row, Column, and Scalar functions.
A scalar function is one that returns a single-valued answer each time it is called. For example, the built-in function SUBSTR() is a scalar function. Scalar UDFs can be either external or sourced. An example of CEILING scalar function.
A column function is one which conceptually is passed a set of like values (a column) and returns a single-valued answer.
These are also sometimes called aggregating functions in DB2. An example of a column function is the built-in function AVG(). An external column UDF cannot be defined to DB2, but a column UDF which is sourced upon one of the built-in column functions can be defined. This is useful for distinct types.
For example, if there is a distinct type SHOE_SIZE defined with base type INTEGER, a UDF AVG(SHOE_SIZE) which is sourced on the built-in function AVG(INTEGER) could be defined, and it would be a column function.
A row function is a function that returns one row of values. It may only be used as a transform function, mapping attribute values of a structured type into values in a row.
A row function must be defined as an SQL function.
A table function is a function that returns a table to the SQL statement which references it. It may only be referenced in the FROM clause of a SELECT.
Such a function can be used to apply SQL language processing power to data which is not DB2 data, or to convert such data into a DB2 table.
It could, for example, take a file and convert it to a table, sample data from the World Wide Web and tabularize it, or access a Lotus Notes database and return information about mail messages, such as the date, sender, and the text of the message.
This information can be joined with other tables in the database. A table function can be defined as an external function or as an SQL function (a table function cannot be a sourced function).
A final note there are differences between Scalar and Aggregate functions.
What is the function signature?
A function is identified by its schema, a function name, the number of parameters and the data types of its parameters.
This is called a function signature which must be unique within the database.
There can be more than one function with the same name in a schema provided that the number of parameters or the data types of the parameters is different.
A function name for which there are multiple function instances is called an overloaded function.
A function name can be overloaded within a schema, in which case there is more than one function by that name in the schema (which of necessity have different parameter types).
A function name can also be overloaded in a SQL path, in which case there is more than one function by that name in the path, and these functions do not necessarily have different parameter types.
The Overloaded functions are differentiated by the number and types of parameters that each function takes.
Related Posts
Srinimf 