SQL 
|
| SQL | | SQL Functions |
Standard
AggregationEXISTS function tests whether a row is returned by a subquery.
| |
TODAY(0)+0, creating an expression which is equivalent to a
simple specification of the variable. For example;
SELECT BIRTHDAY FROM EMPLOYEEThis creates a
BIRTHDAY column which is a date:
SELECT BIRTHDAY+365 FROM EMPLOYEEThis 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 )
MISS
Returns the original value of the named variable even if this would otherwise be flagged as a missing value. If the variable contains undefined,
then missing is returned. This function can be used with all types of database or tabfile variables; string values are returned for character,
categoricals, dates and times. (
MISSING is a synonym.)num = MISS (column)
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 )
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:
UNIQUE is specified then only unique values
are used to calculate the mean.COUNT ( [ UNIQUE ] col | * )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)
LAST (col)MAX
(col)MIN (col)STD
( [ UNIQUE ]
numeric_col)UNIQUE is specified, then
only the unique values are used in calculating the standard deviation.SUM ( [ UNIQUE ]
numeric_col )UNIQUE is specified, then only the unique values
are summed.| |