A scalar function can be used wherever an expression can be used.
The restrictions on the use of aggregate functions do not apply to scalar functions, because a scalar function is applied to single set of parameter values rather than to sets of values.
The argument of a scalar function can be a function.
However, the restrictions that apply to the use of expressions and aggregate functions also apply when an expression or aggregate function is used within a scalar function.
For example, the argument of a scalar function can be a aggregate function only if a aggregate function is allowed in the context in which the scalar function is used.

If the argument of a scalar function is a string from a column with a field procedure, the function applies to the decoded form of the value and the result of the function does not inherit the field procedure.

Example:
The following SELECT statement calls for the employee number, last name, and age of each employee in department D11 in the sample table DSN8910.EMP.
To obtain the ages, the scalar function YEAR is applied to the expression:

   
           CURRENT DATE - BIRTHDATE

           SELECT  EMPNO, LASTNAME, YEAR(CURRENT DATE - BIRTHDATE)
           FROM    DSN8910.EMP
           WHERE  WORKDEPT = 'D11'

• ABS
  The ABS function returns the absolute value of a number.


• ACOS
  The ACOS function returns the arc cosine of the argument as an angle, expressed in radians.
  The ACOS and COS functions are inverse operations.


• ADD_MONTHS
  The ADD_MONTHS function returns a date that represents expression plus a specified number of months.


• NAO ASCII
  The ASCII function returns the leftmost character of the argument as an integer.


• NAO ASCII_CHR
  The ASCII_CHR function returns the character that has the ASCII code value that is specified by the argument.


• NAO ASCII_STR
  The ASCII_STR function returns an ASCII version of the string in the system ASCII CCSID.
  The system ASCII CCSIS is the SBCS ASCII CCSID on a MIXED=NO system or the MIXED ASCII CCSID on a MIXED=YES system.


• ASIN
  The ASIN function returns the arc sine of the argument as an angle, expressed in radians.
  The ASIN and SIN functions are inverse operations.


• ATAN
  The ATAN function returns the arc tangent of the argument as an angle, expressed in radians.
  The ATAN and TAN functions are inverse operations.


• ATANH
  The ATANH function returns the hyperbolic arc tangent of a number, expressed in radians.
  The ATANH and TANH functions are inverse operations.


• ATAN2
  The ATAN2 function returns the arc tangent of x and y coordinates as an angle, expressed in radians.


• NAO BIGINT
  The BIGINT function returns a big integer representation of either a number or a character or graphic string representation of a number.


• NAO BINARY
  The BINARY function returns a BINARY (fixed-length binary string) representation of a string of any type or of a row ID type.


• BLOB
  The BLOB function returns a BLOB representation of a string of any type or of a row ID type.


• CCSID_ENCODING
  The CCSID_ENCODING function returns a string value that indicates the encoding scheme of a CCSID that is specified by the argument.


• CEILING
  The CEILING function returns the smallest integer value that is greater than or equal to the argument.


• CHAR
  The CHAR function returns a fixed-length character string representation of the argument.


• NAO CHARACTER_LENGTH
  The CHARACTER_LENGTH function returns the length of the first argument in the specified string unit.


• CLOB
  The CLOB function returns a CLOB representation of a string.


• COALESCE
  The COALESCE function returns the value of the first nonnull expression.


• NAO COLLATION_KEY
  The COLLATION_KEY function returns a varying-length binary string that represents the collation key of the argument in the specified collation.


• NAO COMPARE_DECFLOAT
  The COMPARE_DECFLOAT function returns a SMALLINT value that indicates whether the two arguments are equal or unordered, or whether one argument is greater than the other.


• CONCAT
  The CONCAT function combines two compatible string arguments.


• NAO CONTAINS
  The CONTAINS function searches a text search index using criteria that are specified in a search argument and returns a result about whether or not a match was found.


• COS
  The COS function returns the cosine of the argument, where the argument is an angle, expressed in radians.
  The COS and ACOS functions are inverse operations.


• COSH
  The COSH function returns the hyperbolic cosine of the argument, where the argument is an angle, expressed in radians.


• DATE
  The DATE function returns a date that is derived from a value.


• DAY
  The DAY function returns the day part of a value.


• DAYOFMONTH
  The DAYOFMONTH function returns the day part of a value.
  The function is similar to the DAY function, except DAYOFMONTH does not support a date or timestamp duration as an argument.


• DAYOFWEEK
  The DAYOFWEEK function returns an integer, in the range of 1 to 7, that represents the day of the week, where 1 is Sunday and 7 is Saturday.
  The DAYOFWEEK function is similar to the DAYOFWEEK_ISO function.


• DAYOFWEEK_ISO
  The DAYOFWEEK_ISO function returns an integer, in the range of 1 to 7, that represents the day of the week, where 1 is Monday and 7 is Sunday.
  The DAYOFWEEK_ISO function is similar to the DAYOFWEEK function.


• DAYOFYEAR
  The DAYOFYEAR function returns an integer, in the range of 1 to 366, that represents the day of the year, where 1 is January 1.


• DAYS
  The DAYS function returns an integer representation of a date.


• DBCLOB
  The DBCLOB function returns a DBCLOB representation of a character string value (with the single-byte characters converted to double-byte characters) or a graphic string value.


• NAO DECFLOAT
  The DECFLOAT function returns a decimal floating-point representation of either a number or a character string representation of a number, a decimal number, an integer, a floating-point number, or a decimal floating-point number.


• NAO DECFLOAT_SORTKEY
  The DECFLOAT_SORTKEY function returns a binary value that can be used when sorting DECFLOAT values.
  The sorting occurs in a manner that is consistent with the IEEE 754R specification on total ordering.


• DECIMAL or DEC
  The DECIMAL function returns a decimal representation of either a number or a character-string or graphic-string representation of a number, an integer, or a decimal number.


• NAO DECRYPT_BINARY, DECRYPT_BIT, DECRYPT_CHAR, and DECRYPT_DB
  The decryption functions return a value that is the result of decrypting encrypted data.
  The decryption functions can decrypt only values that are encrypted by using the ENCRYPT_TDES function.


• DEGREES
  The DEGREES function returns the number of degrees of the argument, which is an angle, expressed in radians.


• NAO DIFFERENCE
  The DIFFERENCE function returns a value, from 0 to 4, that represents the difference between the sounds of two strings, based on applying the SOUNDEX function to the strings.
  A value of 4 is the best possible sound match.


• DIGITS
  The DIGITS function returns a character string representation of the absolute value of a number.


• DOUBLE_PRECISION or DOUBLE
  The DOUBLE_PRECISION and DOUBLE functions returns a floating-point representation of either a number or a character-string or graphic-string representation of a number, an integer, a decimal number, or a floating-point number.


• NAO DSN_XMLVALIDATE
  The DSN_XMLVALIDATE function returns an XML value that is the result of applying XML schema validation to the first argument of the function.
  DSN_XMLVALIDATE can validate XML data that has a maximum length of 2 GB - 1 byte.


• NAO DSN_XMLVALIDATE
  The result of DSN_XMLVALIDATE is a varying-length binary value of up to 50 MB.
  The result of this function is relevant only as input to the XMLPARSE function.


• NAO EBCDIC_CHR
  The EBCDIC_CHR function returns the character that has the EBCDIC code value that is specified by the argument.


• NAO EBCDIC_STR
  The EBCDIC_STR function returns a string, in the system EBCDIC CCSID, that is an EBCDIC version of the string.


• NAO ENCRYPT_TDES
  The ENCRYPT_TDES function returns a value that is the result of encrypting the first argument by using the Triple DES encryption algorithm.
  The function can also set the password that is used for encryption.


• EXP
  The EXP function returns a value that is the base of the natural logarithm (e), raised to a power that is specified by the argument.
  The EXP and LN functions are inverse operations.


• NAO EXTRACT
  The EXTRACT function returns a portion of a date or timestamp, based on its arguments.


• FLOAT
  The FLOAT function returns a floating-point representation of either a number or a string representation of a number.
  FLOAT is a synonym for the DOUBLE function.


• FLOOR
  The FLOOR function returns the largest integer value that is less than or equal to the argument.


• GENERATE_UNIQUE
  The GENERATE_UNIQUE function returns a bit data character string that is unique, compared to any other execution of the same function.


• NAO GETHINT
  The GETHINT function returns a hint for the password if a hint was embedded in the encrypted data.
  A password hint is a phrase that helps you remember the password with which the data was encrypted.
  For example, 'Ocean' might be used as a hint to help remember the password 'Pacific'.


• NAO GETVARIABLE
  The GETVARIABLE function returns a varying-length character-string representation of the current value of the session variable that is identified by the argument.


• GRAPHIC
  The GRAPHIC function returns a fixed-length graphic-string representation of a character string or a graphic string value, depending on the type of the first argument.


• HEX
  The HEX function returns a hexadecimal representation of a value.


• HOUR
  The HOUR function returns the hour part of a value.


• IDENTITY_VAL_LOCAL
  The IDENTITY_VAL_LOCAL function returns the most recently assigned value for an identity column.


• IFNULL
  The IFNULL function returns the first nonnull expression.


• INSERT
  The INSERT function returns a string where, beginning at start in source-string, length characters have been deleted and insert-string has been inserted.


• INTEGER or INT
  The INTEGER function returns an integer representation of either a number or a character string or graphic string representation of an integer.


• JULIAN_DAY
  The JULIAN_DAY function returns an integer value that representsa number of days from January 1, 4713 B.C. (the start of the Julian date calendar) to the date that is specified in the argument.


• LAST_DAY
  The LAST_DAY scalar function returns a date that represents the last day of the month of the date argument.


• LCASE
  The LCASE function returns a string in which all the characters are converted to lowercase characters.


• LEFT
  The LEFT function returns a string that consists of the specified number of leftmost bytes of the specified string units.


• LENGTH
  The LENGTH function returns the length of a value.


• LN
  The LN function returns the natural logarithm of the argument.
  The LN and EXP functions are inverse operations.


• LOCATE
  The LOCATE function returns the position at which the first occurrence of an argument starts within another argument.


• NAO LOCATE_IN_STRING
  The LOCATE_IN_STRING function returns the position at which an argument starts within a specified string.


• LOG10
  The LOG10 function returns the common logarithm (base 10) of a number.


• LOWER
  The LOWER function returns a string in which all the characters are converted to lowercase characters.


• NAO LPAD
  The LPAD function returns a string that is composed of string-expression that is padded on the left, with pad or blanks.
  The LPAD function treats leading or trailing blanks in string-expression as significant.


• LTRIM
  The LTRIM function removes blanks or hexadecimal zeros from the beginning of a string expression.


• MAX
  The MAX scalar function returns the maximum value in a set of values.


• MICROSECOND
  The MICROSECOND function returns the microsecond part of a value.


• MIDNIGHT_SECONDS
  The MIDNIGHT_SECONDS function returns an integer, in the range of 0 to 86400, that represents the number of seconds between midnight and the time that is specified in the argument.


• MIN
  The MIN scalar function returns the minimum value in a set of values.


• MINUTE
  The MINUTE function returns the minute part of a value.


• MOD
  The MOD function divides the first argument by the second argument and returns the remainder.


• MONTH
  The MONTH function returns the month part of a value.


• NAO MONTHS_BETWEEN
  The MONTHS_BETWEEN function returns an estimate of the number of months between two arguments.


• MQPUBLISH
  The MQPUBLISH function publishes a message to the specified MQSeries® publisher, and returns a varying-length character string that indicates whether the function was successful or unsuccessful.


• NAO MQPUBLISHXML
  The MQPUBLISHXML function publishes the XML data in a message to the specified MQSeries publisher.


• MQREAD
  The MQREAD function returns a message from a specified MQSeries location without removing the message from the queue.


• MQREADCLOB
  The MQREADCLOB function returns a message from a specified MQSeries location without removing the message from the queue.


• NAO MQREADXML
  The MQREADXML function returns an XML message from a specified MQSeries location without removing the message from the queue.


• MQRECEIVE
  The MQRECEIVE function returns a message from a specified MQSeries location and removes the message from the queue.


• MQRECEIVECLOB
  The MQRECEIVECLOB function returns a message from a specified MQSeries location and removes the message from the queue.


• NAO MQRECEIVEXML
  The MQRECEIVEXML function returns a message from a specified MQSeries location and removes the message from the queue.


• MQSEND
  The MQSEND function sends data to a specified MQSeries location, and returns a varying-length character string that indicates whether the function was successful or unsuccessful.


• NAO MQSENDXML
  The MQSENDXML function sends XML data to a specified MQSeries location.


• NAO MQSENDXMLFILE
  The MQSENDXMLFILE function sends up to 3 KB of data that is contained in the specified XML file to a specified MQSeries location.


• NAO MQSENDXMLFILECLOB
  The MQSENDXMLFILECLOB function sends up to 1 MB of data that is contained in an XML file to a specified MQSeries location.


• MQSUBSCRIBE
  The MQSUBSCRIBE function registers a subscription to MQSeries messages that are published on a specified topic, and returns a varying-length character string that indicates if the function was successful.


• MQUNSUBSCRIBE
  The MQUNSUBSCRIBE function unsubscribes to MQSeries messages that are published on a specified topic, and returns a varying-length character string that indicates if the function was successful.


• MULTIPLY_ALT
  The MULTIPLY_ALT scalar function returns the product of the two arguments.
  This function is an alternative to the multiplication operator and is especially useful when the sum of the precisions of the arguments exceeds 31.


• NEXT_DAY
  The NEXT_DAY function returns a timestamp that represents the first occurrence of the specified weekday that is after the date argument.


• NAO NORMALIZE_DECFLOAT
  The NORMALIZE_DECFLOAT function returns a DECFLOAT value that is the result of the argument, set to its simplest form.
  That is, a non-zero number that has any trailing zeros in the coefficient has those zeros removed by dividing the coefficient by the appropriate power of ten and adjusting the exponent accordingly.
  A zero has its exponent set to 0.


• NAO NORMALIZE_STRING
  The NORMALIZE_STRING function takes a Unicode string argument and returns a normalized string that can be used for comparison.


• NULLIF
  The NULLIF function returns the null value if the two arguments are equal; otherwise, it returns the value of the first argument.


• NAO OVERLAY
  The OVERLAY function returns a string that is composed of one argument that is inserted into another argument at the same position where some number of bytes have been deleted.


• NAO POSITION
  The POSITION function returns the position of the first occurrence of an argument within another argument, where the position is expressed in terms of the string units that are specified.


• POSSTR
  The POSSTR function returns the position of the first occurrence of an argument within another argument.


• POWER
  The POWER® function returns the value of the first argument to the power of the second argument.


• NAO QUANTIZE
  The QUANTIZE function returns a DECFLOAT value that is equal in value (except for any rounding) and sign to the first argument and that has an exponent that is set to equal the exponent of the second argument.


• QUARTER
  The QUARTER function returns an integer between 1 and 4 that represents the quarter of the year in which the date resides.
  For example, any dates in January, February, or March return the integer


• RADIANS
  The RADIANS function returns the number of radians for an argument that is expressed in degrees.


• RAISE_ERROR
  The RAISE_ERROR function causes the statement that invokes the function to return an error with the specified SQLSTATE (along with SQLCODE -438) and error condition.
  The RAISE_ERROR function always returns the null value with an undefined data type.


• RAND
  The RAND function returns a random floating-point value between 0 and 1.
  An argument can be specified as an optional seed value.


• REAL
  The REAL function returns a single-precision floating-point representation of either a number or a string representation of a number.


• REPEAT
  The REPEAT function returns a character string that is composed of an argument that is repeated a specified number of times.


• REPLACE
  The REPLACE function replaces all occurrences of search-string in source-string with replace-string.
  If search-string is not found in source-string, source-string is returned unchanged.


• NAO RID
  The RID function returns the record ID (RID) of a row.
  The RID is used to uniquely identify a row.


• RIGHT
  The RIGHT function returns a string that consists of the specified number of rightmost bytes or specified string unit from a string.


• ROUND
  The ROUND function returns a number that is rounded to the specified number of places to the right or left of the decimal place.


• ROUND_TIMESTAMP
  The ROUND_TIMESTAMP scalar function returns a timestamp that is rounded to the unit that is specified by the timestamp format string.


• ROWID
  The ROWID function returns a row ID representation of its argument.


• NAO RPAD
  The RPAD function returns a string that is padded on the right with blanks or a specified string.


• RTRIM
  The RTRIM function removes blanks or hexadecimal zeros from the end of a string expression.


• NAO SCORE
  The SCORE function searches a text search index using criteria that are specified in a search argument and returns a relevance score that measures how well a document matches the query.


• SECOND
  The SECOND function returns the seconds part of a value.


• SIGN
  The SIGN function returns an indicator of the sign of the argument.


• SIN
  The SIN function returns the sine of the argument, where the argument is an angle, expressed in radians.


• SINH
  The SINH function returns the hyperbolic sine of the argument, where the argument is an angle, expressed in radians.


• SMALLINT
  The SMALLINT function returns a small integer representation either of a number or of a string representation of a number.


• NAO SOUNDEX
  The SOUNDEX function returns a 4-character code that represents the sound of the words in the argument.
  The result can be compared to the results of the SOUNDEX function of other strings.


• NAO SOAPHTTPC and SOAPHTTPV
  The SOAPHTTPC function returns a CLOB representation of XML data that results from a SOAP request to the Web service that is specified by the first argument.
  The SOAPHTTPV function returns a VARCHAR representation of XML data that results from a SOAP request to the web service that is specified by the first argument.


• NAO SOAPHTTPNC and SOAPHTTPNV
  The SOAPHTTPNC and SOAPHTTPNV functions allow you to specify a complete SOAP message as input and to return complete SOAP messages from the specified Web service.
  The returned SOAP messages are CLOB or VARCHAR representations of the returned XML data.


• SPACE
  The SPACE function returns a character string that consists of the number of SBCS blanks that the argument specifies.


• SQRT
  The SQRT function returns the square root of the argument.


• STRIP
  The STRIP function removes blanks or another specified character from the end, the beginning, or both ends of a string expression.


• SUBSTR
  The SUBSTR function returns a substring of a string.


• SUBSTRING
  The SUBSTRING function returns a substring of a string.


• TAN
  The TAN function returns the tangent of the argument, where the argument is an angle, expressed in radians.


• TANH
  The TANH function returns the hyperbolic tangent of the argument, where the argument is an angle, expressed in radians.


• TIME
  The TIME function returns a time that is derived from a value.


• TIMESTAMP
  The TIMESTAMP function returns a timestamp that is derived from its argument or arguments.


• NAO TIMESTAMPADD
  The TIMESTAMPADD function returns the result of adding the specified number of the designated interval to the timestamp value.


• TIMESTAMP_FORMAT
  The TIMESTAMP_FORMAT function returns a timestamp that is based on interpreting the input string by using the specified format.


• NAO TIMESTAMP_ISO
  The TIMESTAMP_ISO function returns a timestamp value that is based on a date, a time, or a timestamp argument.


• NAO TIMESTAMPDIFF
  The TIMESTAMPDIFF function returns an estimated number of intervals of the type that is defined by the first argument, based on the difference between two timestamps.


• NAO TOTALORDER
  The TOTALORDER function returns an ordering for DECFLOAT values.
  The TOTALORDER function returns a small integer value that indicates how expression1 compares with expression2.


• TRANSLATE
  The TRANSLATE function returns a value in which one or more characters of the first argument might have been converted to other characters.


• TRUNCATE or TRUNC
  The TRUNCATE function returns the first argument, truncated as specified.
  Truncation is to the number of places to the right or left of the decimal point this is specified by the second argument.


• TRUNC_TIMESTAMP
  The TRUNC_TIMESTAMP function returns a timestamp that is the expression, truncated to the unit that is specified by the format-string.


• UCASE
  The UCASE function returns a string in which all the characters have been converted to uppercase characters, based on the CCSID of the argument.
  The UCASE function is identical to the UPPER function.


• NAO UNICODE
  The UNICODE function returns the Unicode UTF-16 code value of the leftmost character of the argument as an integer.


• NAO UNICODE_STR
  The UNICODE_STR function returns a string in Unicode UTF-8 or UTF-16, depending on the specified option.
  The string represents a Unicode encoding of the input string.


• UPPER
  The UPPER function returns a string in which all the characters have been converted to uppercase characters.


• NAO VALUE
  The VALUE function returns the value of the first non-null expression.


• NAO VARBINARY
  The VARBINARY function returns a VARBINARY (varying-length binary string) representation of a string of any type.


• VARCHAR
  The VARCHAR function returns a varying-length character string representation of the value specified by the first argument.
  The first argument can be a character string, a graphic string, a datetime value, an integer number, a decimal number, a floating-point number, or a row ID value.


• VARCHAR_FORMAT
  The VARCHAR_FORMAT function returns a character representation of a timestamp in the format that is indicated by the argument.


• VARGRAPHIC
  The VARGRAPHIC function returns a varying-length graphic string representation of a the first argument.
  The first argument can be a character string value or a graphic string value.


• WEEK
  The WEEK function returns an integer in the range of 1 to 54 that represents the week of the year.
  The week starts with Sunday, and January 1 is always in the first week.


• WEEK_ISO
  The WEEK_ISO function returns an integer in the range of 1 to 53 that represents the week of the year.
  The week starts with Monday and includes seven days.
  Week 1 is the first week of the year that contains a Thursday, which is equivalent to the first week that contains January 4.


• NAO XMLATTRIBUTES
  The XMLATTRIBUTES function constructs XML attributes from the arguments.
  This function can be used as an argument only for the XMLELEMENT function.


• NAO XMLCOMMENT
  The XMLCOMMENT function returns an XML value with a single comment node from a string expression.
  The content of the comment node is the value of the input string expression, mapped to Unicode (UTF-8).


• NAO XMLCONCAT
  The XMLCONCAT function returns an XML sequence that contains the concatenation of a variable number of XML input arguments.


• NAO XMLDOCUMENT
  The XMLDOCUMENT function returns an XML value with a single document node and zero or more nodes as its children.
  The content of the generated XML document node is specified by a list of expressions.


• NAO XMLELEMENT
  The XMLELEMENT function returns an XML value that is an XML element node.


• NAO XMLFOREST
  The XMLFOREST function returns an XML value that is a sequence of XML element nodes.


• NAO XMLNAMESPACES
  The XMLNAMESPACES function constructs namespace declarations from the arguments.
  This function can be used as an argument only for specific functions, such as the XMLELEMENT function and the XMLFOREST function.


• NAO XMLPARSE
  The XMLPARSE function parses the argument as an XML document and returns an XML value.


• NAO XMLPI
  The XMLPI function returns an XML value with a single processing instruction node.


• NAO XMLQUERY
  The XMLQUERY function returns an XML value from the evaluation of an XPath expression, by using specified input arguments, a context item, and XPath variables.


• NAO XMLSERIALIZE
  The XMLSERIALIZE function returns a serialized XML value of the specified data type that is generated from the first argument.


• NAO XMLTEXT
  The XMLTEXT function returns an XML value with a single text node that contains the value of the argument.


• YEAR
  The YEAR function returns the year part of a value that is a character or graphic string. The value must be a valid string representation of a date or timestamp.