| ||
| SQL | | SQL Functions |
Standard
AggregationEXISTS, which is used to
test whether a row is returned by a subquery.
Standard Functions
The expressions in the arguments must be of the correct type (numeric or string)
for the function being used. Expressions can contain constants, variables,
computations and other functions. Functions can be nested as necessary.
Enclose string constants in single quotes. Date, time and categorical variables
can be treated as an integer or as a string depending on the context. A
specification of one of these variables retains the original data type.
However, if these are referred to in a numeric function, the integer value is
returned; if referred to in a string function, the string value is returned.
Once a variable has been used in a computation or expression in this way, the
resulting column is an integer or a string rather than being a date, time or
categorical column. SQL eliminates null computations, for example
TODAY(0)+0, creating an expression which is equivalent to a
simple specification of the variable. For example;SELECT BIRTHDAY FROM EMPLOYEE
This creates a BIRTHDAY column which is a date:SELECT BIRTHDAY+365 FROM EMPLOYEE
This creates a "BIRTHDAY+365" column which is an integer. The
SQL functions are:ABS
Returns the absolute value of the numeric expression.
num = ABS( expression )
ALL
Tests against the values in the value_list. Returns 1
(true) if matches every item. Equivalent to individual tests against each item
in the list joined by
AND operators. ALL is
used particularly to test against sets of values returned by subqueries.value = ALL ( value_list | subquery )
ANY
Tests against the values in the value_list. Returns 1
(true) if matches any item. Equivalent to individual tests against each item in
the list joined by
OR operators. ANY is
used particularly to test against sets of values returned by subqueries.value = ANY ( value_list | subquery )
CDATE
Returns the date integer for the date specified by the date string in
the first argument. The second argument is the date map. If the second
argument is omitted, the current value of the system parameter
DATE is used as the date map.num = CDATE ( date-string [, date_map])
CTIME
Returns the time integer for the time specified by the time string in
the first argument. The second argument is the time map. If the second
argument is omitted, the current value of the system parameter
TIME is used as the time map.num = CTIME ( time_string [, time_map ])
DATEC
Returns a date string for the date integer in the first argument.
The second argument is the date conversion map. If the second argument is
omitted, the current value of the system parameter
DATE is
used as the date map.str = DATEC ( date_integer, date_map)
EXISTS
Tests whether a subquery returns any rows. Returns 1 (true) if one
or more rows are selected in the subquery.
NOT can be used to
test for the opposite condition. Specify an asterisk in the subquery as the
returned variables when using the EXISTS function .
Note
that this function is different from the VisualPQL EXISTS function.
The SQL EXISTS function is the ANSI standard function.SELECT .... WHERE EXISTS ( SELECT * .... )
SELECT .... WHERE NOT EXISTS( SELECT * ....)
INT
Returns the truncated integer value for the numeric expression.
num = INT ( expression )
LEN
Returns the number of characters (including trailing blanks) in the
string expression.
num = LEN( string )
LOWER
Returns the string with all uppercase letters converted to their
lowercase equivalent.
str = LOWER( string )
MAXIMUM
Returns the maximum of the two values supplied.
num = MAXIMUM ( value1, value2 )
MINIMUM
Returns the minimum of the two values supplied.
num = MINIMUM ( value1, value2 )
MOD
Returns the remainder of the integer division of the number by the
divisor.
num = MOD ( number, divisor )
NOW
Returns the current wall clock time as a time integer. The argument
is a dummy argument.
num = NOW (0)
NUM
Returns the numeric equivalent of the number stored in the specified
string.
num = NUM ( string )
NVL
Returns the first argument if the specified column does not contain
missing values. If the column does contain missing values, then the missing
value is used to determine which of the remaining arguments is returned.
The
system value Undefined returns the next argument (the second) and any specified
missing values return the argument that corresponds to the position in the
definition of the missing values.
The first missing value defined returns the
3rd argument, the next missing value defined returns the 4th argument and the
third missing value defined returns the 5th argument, etc. If this is a
database variable, then up to three missing values can be defined.
If this is a
table column, then up to 127 missing values can be defined.The values returned may be string or numeric.
num = NVL ( column, arg1, arg2, ...)
str = NVL ( column, arg1, arg2, ...)
RECCOUNT
Returns a count of occurrences of this record in this case. The rectype can
be the record name or record number.
num = RECCOUNT ( rectype )
RND
Returns the integer value rounded to the nearest integer. The
optional second argument specifies the number of decimal places to be rounded to
instead of the nearest integer.
num = RND ( number [, digits ] )
SBST
Returns the substring of the specified string starting at a
particular position and continuing for the specified length.
str = SBST ( string, start, length )
SIGN
Returns the sign of the second argument times the absolute value of
the first argument.
num = SIGN ( number, sign )
TIMEC
Returns a time string for the time specified by the first argument.
The second argument is the time map. (Defaults to the system parameter
TIME.)str = TIMEC ( time_integer, time_map )
TODAY
Returns the current calendar date as a Julian integer. The argument
is a dummy argument.
num = TODAY (0)
TRIM
Returns the specified string with trailing blanks removed.
str = TRIM ( string )
UPPER
Returns the string specified with all lowercase letters converted to
their uppercase equivalent.
str = UPPER ( string )
VALLAB
Returns the value label associated with the current value of the
specified column_name.
str = VALLAB ( column_name )
Aggregation functions
Aggregation functions return a single value for all of the relevant rows
processed. See SELECT
for the effect aggregation functions have on the
SELECT process. The aggregation functions are:AVG ([UNIQUE]
numeric_col)
Returns the average or mean value of
the non-missing values for numeric columns. If
UNIQUE is
specified then only unique values are used to calculate the mean.COUNT ( [ UNIQUE ] col | * )
Returns the number of non-missing
values encountered. If
UNIQUE is specified, then only the
unique values add to the count. An asterisk as the argument returns the number
of all rows selected regardless of whether the values are valid, missing or
undefined.FIRST ( col)
Returns the first non-missing value encountered. The type of variable
returned corresponds to the type of the variable being referenced.
LAST (col)
Returns the last non-missing value encountered. The type of variable
returned corresponds to the type of the variable being referenced.
MAX (col)
Returns the maximum non-missing value encountered. The type of
variable returned corresponds to the type of the variable being referenced.
MIN (col)
Returns the minimum value of the non-missing values. The type of
variable returned corresponds to the type of the variable being referenced.
STD( [ UNIQUE ]
numeric_col)
Returns the standard deviation of
the non-missing numeric values. If
UNIQUE is specified, then
only the unique values are used in calculating the standard deviation.SUM ( [ UNIQUE ]
numeric_col )
Returns the sum of the non-missing
values. If
UNIQUE is specified, then only the unique values
are summed.