The ultimate notes on SQL functions to make you stronger

Overloaded SQL function
[Overloaded SQL function]
The best part in SQL programming and your success depends how better you can use built-in functions in SQL. These are the step by step explanations for  each function that is available in DB2 or any other RDBMS database SQL.

A scalar function is one which 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 for 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 SHOESIZE defined with base type INTEGER, a UDF AVG(SHOESIZE) 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 which 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 which 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 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 are 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.


Author: Srini

Experienced software developer. Skills in Development, Coding, Testing and Debugging. Good Data analytic skills (Data Warehousing and BI). Also skills in Mainframe.