User's Guide

Part VI. SQL Anywhere Reference

Chapter 42. Watcom-SQL Functions

Purpose

Miscellaneous functions perform operations on arithmetic, string or date/time expressions, including the return values of other functions.

Syntax

     Miscellaneous function:
          ARGN( integer-expr, expression [ , ...] )
          | COALESCE ( expression, expression [ , ...] )
          | ESTIMATE ( column-name [ , number [, relation-string ] ] )
          | ESTIMATE_SOURCE ( column-name [, number [ , relation-string] ]
          | IFNULL ( expression, expression [ , expression ] )
          | INDEX_ESTIMATE( column-name, number [ , relation-string ] )
          | EXPERIENCE_ESTIMATE( column-name, number [ , relation-string ]) ]
          | ISNULL ( expression, expression [ , ... ] )
          | NUMBER ( * )
          | PLAN ( string-expr )
          | TRACEBACK ( * )

See also

• Aggregate functions
• Numeric functions
• String functions
• Date and time functions
• Data type conversion functions
• System functions

Description

ARGN( integer-expr, expression [, ...] ) Using the value of integer-expr as n, return the n'th argument (starting at 1) from the remaining list of arguments.

COALESCE( expression, expression [ ... , expression] ) Returns the value of the first expression that is not NULL.

ESTIMATE( column-name [, number [, relation-string]] ) The relation-string must be a comparison operator enclosed in single quotes; the default is =. If number is specified, the function returns as a REAL the percentage estimate the query optimizer uses for the following condition:

     column-name relation number

If number is not specified, the function returns the estimate used by the query-optimizer for the following condition:

     column-name relation expression

The function returns NULL if the relation-string is not valid. For example, the following query returns the percentage estimate for employee ID numbers being greater than 200:

     SELECT DISTINCT ESTIMATE( emp_id, 200, '>' )
     FROM employee

ESTIMATE_SOURCE( column-name [, number [, relation-string]] ) This function is the same as the ESTIMATE function, except that it returns one of the strings Column, Value, or Index, where:

• Column means the estimate stored for the column
• Value means the estimate for a particular value, stored in the frequency table
• Index means the estimate derived from an index on the column

EXPERIENCE_ESTIMATE(column-name [, number [, relation-string]]) This function is the same as the ESTIMATE function, except that it looks only in the frequency table.

IFNULL( expression1, expression2 [, expression3] ) If the first expression is the NULL value, then the second expression is returned. Otherwise, the value of the third expression is returned if it was specified. If there was no third expression and the first expression is not NULL then the NULL value is returned.

INDEX_ESTIMATE( column-name [, number [, relation-string]] ) This function is the same as the ESTIMATE function, except that it looks only in an index.

ISNULL( expression, expression [ ... , expression] ) Same as the COALESCE function.

NUMBER( * ) Generates numbers starting at 1 for each successive row in the results of the query. Although the NUMBER(*) function is useful for generating primary keys when using the insert from select statement (see "INSERT statement"), the AUTOINCREMENT column is a preferred mechanism for generating sequential primary keys. For information on the AUTOINCREMENT default, see "CREATE TABLE statement".

You should not use the NUMBER( * ) function anywhere but in a select-list. If you do use NUMBER( * ) rather than the preferred AUTOINCREMENT, you should check your results carefully, as the behavior is not reliable in several circumstances. For example, including the function in a WHERE clause or a HAVING clause produces unpredictable results, and you should not include NUMBER( * ) in a UNION operation.

In Embedded SQL, care should be exercised when seeking a cursor that references a query containing a NUMBER(*) function. In particular, this function returns negative numbers when a database cursor is positioned relative to the end of the cursor (an absolute seek with a negative offset).

PLAN( string-expr ) Returns the optimization strategy of the SELECT statement string-expr as a string.

TRACEBACK( * ) Returns a string containing a traceback of the procedures and triggers that were executing when the most recent exception (error) occurred. This is useful for debugging procedures and triggers. To use the traceback function, enter the following after an error occurs while executing a procedure.

     SELECT TRACEBACK (*)