Viewpoint Functions
Some of the more commonly used functions are described below. For a complete list of all functions refer to the Sequel 10 SQL Reference Guide.
Alphanumeric
|
|
========================================================
Character
|
|
|
|
========================================================
Date/Time
========================================================
Grouping
|
|
========================================================
HTML
|
|
|
|
========================================================
Multisystem
========================================================
Numeric
|
|
|
|
========================================================
Where/Having
|
ALL
|
ANY
|
||
|
EXISTS
|
IN
|
||
|
SOME
|
|
========================================================
ABS
Returns the unsigned value for a single numeric field,
constant, or expression. 
More...
ABS(expression)
ACCUM
Create a running sub-total and return a floating point result. Example
ACCUM(expression,charexp) or ACCUM(expression)
ACOS
Arc cosine of the argument returned in radians. More...
ACOS(expression)
ALL (subquery)
A simple variation of the basic subquery comparison allows several records
to be included by the subquery. By using the reserved word ALL prior to
the subquery, a set
of values can be compared against a field or expression result, much like
the IN test. More...
AND
One of four Boolean operators used in the WHERE clause for record selection.
More...
The result of an AND condition is true if both comparisons are also true.
ANY (subquery)
A simple variation of the basic subquery comparison allows several records
to be included by the subquery. By using the reserved word ANY prior to
the subquery, a set
of values can be compared against a field or expression result, much like
the IN test. More...
ASIN
Arc sine of the argument returned in radians. More...
ASIN(expression)
ATAN
Arc tangent of the argument returned in radians. More...
ATAN(expression)
ATAN2
Calculate the arc tangent of y/x. More...
ATAN2(y,x)
ATANH
Hyperbolic arc tangent of the argument returned in radians. More...
ATANH(expression)
AVG (numeric)
Returns the average value from a list of fields, constants, or expressions.
More...
AVG(expression,expression...)
AVG (grouping)
Computes the averages for the specified expression across all records
in the group. The result is null only if all values in the group are null.
More...
AVG([DISTINCT | ALL ] expression)
BCAT
CAT and insert one blank at concatenation. More...
BCAT(expression,expression,expression...)
BETWEEN
A comparison operator used in the WHERE clause for a range of values.
More...
CASE
Conditional results can be created with a CASE expression. More...
The CASE expression has two forms. The first simpler form, lists an expression to be evaluated, then one or more WHEN-THEN clauses, and an optional ELSE clause.
CASE expression
WHEN expression THEN expression
[WHEN expression THEN expression
[ELSE expression]]
END
The more complex form to the CASE expression lets you specify separate conditional expressions and non-equal tests in each WHEN clause.
CASE WHEN search-condition THEN expression
[WHEN search-condition THEN expression
[ELSE expression]] END
CAT
The CAT function is the equivalent to the concatenation operator (CAT
or ||) and combines the results of the alphanumeric expressions without
removing leading or trailing blanks. More...
CAT(expression,expression,expression...)
CEIL
Rounds the expression upwards (away from zero) to the number of decimal
digits specified.
More...
CEIL(expression [,digits])
CENTER
Center a field. Example
CENTER(charexp,exp)
CHAR (numeric)
Convert numeric to character. Accepts a numeric value(binary, packed,
or zoned) and on optional decimal point character. Use CHAR when you want
a left justified result with leading zeros removed and a decimal point
inserted when needed. More...
CHAR(expression[,decimal-character])
CHAR (date/time)
Converts a date type expression to a fixed length string in the requested format. Type must be a recognized date/time type.
CHAR(expression [,type])
CHAR2NUM
Right adjust a left-adjusted numeric string so it can be converted to
decimal.
Example
CHAR2NUM(charexp, exp)
CONTAINS
Search operator (WHERE clause) useful for searching character fields
(and expressions) to determine if they include a specific sequence of
characters.
More...
COS
Cosine of radian argument. The COS and ACOS functions are inverse operations.
The result is in the range of -1 to 1. More...
COS(expression)
COSH
Hyperbolic cosine of radian argument. More...
COSH(expression)
COT
Cotangent of radian argument.
More...
COT(expression)
COUNT (grouping)
Return the number of records within the group. More...
COUNT(*)or COUNT([DISTINCT | ALL ] expr)
CVTDATE
Converts expression to date data type. Expression can be character or
numeric. Type tells Sequel how to interpret the expression.
More...
CVTDATE(expression,type)
Use CVTDATE on separate century, year month and day fields.
CVTDATE(cc,yy,mm,dd)
CVTDATE(yy,mm,dd)
CVTDATE(yyyy,mm,dd)
CVTTIME
Creates a time value from either a single 6-digit value or from three
2-digit values. Values may be supplied in either numeric or character
form.
More...
CVTTIME(hhmmss)
-or-
CVTTIME(hh,mm,ss)
CYYDDD
Convert a date value to a decimal(6,0) value of the form CYYDDD. Example
CYYDDD(date)
CYYMMDD
Convert a date value to a decimal(7,0) value of the form CYYMMDD. Example
CYYMMDD(date)
DATE
Creates a date value from the expression argument. The result is null
if the expression value is null.
More...
DATE(expression)
DATE
Creates a date value from the expression argument. For use with *LOCALSYS, ADS and JDE connections. Expression must be a character or numeric field in a recognizable format. Type tells DATE how to interpret the expression.
DATE(expression, 'type')
DAY
Returns the day (2,0) part of a date, timestamp, date duration, or timestamp
duration.
More...
DAY(expression)
DAYOFWEEK
Returns values from 1 to 7 representing the days of the week for the
given date expression. Sunday corresponds to a value of 1. More...
DAYOFWEEK(expression)
DAYOFYEAR
Returns values from 1 to 366 representing the days of the year for the
given date expression. January the 1st
corresponds to a value of 1.
More...
DAYOFYEAR(expression)
DAYS
Converts a date type expression to a number that represents the number
of days from the beginning of the system calendar. More...
DAYS(expression)
DDMMYY
Convert a date value to a decimal(6,0) value of the form DDMMYY. Example
DDMMYY(date)
DDMMYYYY
Convert a date value to a decimal(8,0) value of the form DDMMYYYY Example
DDMMYYYY(date)
DECIMAL
Returns a result that is truncated to a packed value. More...
DECIMAL(expression[,len[,dec]])
DIFFERENCE
Returns an integer, 0 through 4, which represents the relative phonetic
difference between two character expressions. More...
DIFFERENCE(char-expression, char-expression)
DIGITS
Convert numeric to character. Accepts a numeric value(binary, packed,
or zoned) and returns a character string representing its unsigned value.
Use DIGITS when you want a fixed length result with leading zeros and
no decimal editing. More...
DIGITS(expression)
DIST_KM
Display the straight-line distance between two locations in kilometers.
Supply starting latitude and longitude and ending latitude and longitude
as 8,6 numeric values (e.g. 42.051927).
More...
DIST_KM(latitude1, logitude1, latitude2, longitude2)
DIST_MILES
Display the straight-line distance between two locations in miles. Supply
starting latitude and longitude and ending latitude and longitude as 8,6
numeric values (e.g. -88.048140). More...
DIST_MILES(latitude1, longitude1, latitude2, longitude2)
DIST_REF_KM
Display the straight-line distance between two locations in kilometers.
Supply starting and ending zip codes as 5 position character values (e.g.
'60173'). Function uses a reference file (sequel/distance) pre-loaded
with U.S. Dept. of Commerce data for US zip codes. More...
DIST_REF_KM(zipcode1, zipcode2)
DIST__REF_MILES
Display the straight-line distance between two locations in miles. Supply
starting and ending zip codes as 5 position character values (e.g. '60173').
Function uses a reference file (sequel/distance) pre-loaded with U.S.
Dept. of Commerce data for US zip codes. More...
DIST_REF_MILES(zipcode1, zipcode2)
DTAARA
Returns a variable length character string up to 200 characters long containing the extracted substring from the indicated data area. Only the first 2000 positions of the data area are accessible. The 'object name' string can contain a fully qualified name (lib/obj) or an unqualified name, or one of the special values *LDA, *GDA, or *PDA.
DTAARA(["objname",] start, length)
DURATION
Accepts two time stamps as input and returns the practical number of units between them.
Specify a unit value (upper or lower case) of ‘microseconds’, ‘seconds’, ‘minutes’, ‘hours’, ‘days’, ‘weeks’, ‘months’, or ‘years’.
DURATION('unit', startTIMESTAMP, endTIMESTAMP)
EDIT
Edit a decimal value using an edit code/word and return a string result.
Example
EDIT(dec,len,int,charexp)
EXISTS (subquery)
The EXISTS test is the most powerful subquery operator. It is used simply
to determine if the subquery that follows it returns any rows at all.
More...
EXP
A special function that allows you to raise the irrational value e
(2.71828&) exponentially.
More...
EXP(expression)
EXP10
A special function that allows you to raise 10 exponentially. More...
EXP10(expression)
FLOAT
Cast numeric expression as single or double precision floating point
number. More...
FLOAT(expression[,len])
FLOOR
Rounds the expression downwards (towards zero) to the number of decimal
digits specified.
More...
FLOOR(expression[,digits])
GETLAST
Return the remainder of a string after the last occurrence of a specified character.
GETLAST(expr, character)
GETLASTLOC
Return the position of the last occurrence of a specified character in a string.
GETLASTLOC(expr, character)
Examples (using the email address - a.b.c@company.com):
|
GETLAST(email, “.”) |
returns 'com' | |
| GETLASTLOC(email, “.”) | 14 |
These two functions used with SST or SUBSTRING create a very powerful string manipulation tool.
GREATEST
Returns the highest value from within a list of fields, constants, or
expressions.
More...
GREATEST(expression, expression&)
HASH
Returns the partition number associated with one or more values. More...
HASH(expression, expression, expression...)
HEX
Convert an alphanumeric value to a hexadecimal (base 16) string. The
result is a string that is twice as long as the character expression.
The string is not prefaced
with a X to denote its nature as a hexadecimal string. More...
HEX(expression)
HOUR
Returns the hour portion of a time, timestamp, time duration, or timestamp
duration.
More...
HOUR(expression)
HREF
This function will build the HREF tag needed to create a link to another
view, report, script, or query object. More...
HREF(item,object[,library[,variable1,value1[,variable2,value2,...]]])
HYPERLINK
This function creates hyperlinks in your view results to display any
type of Windows or Web-based object on the Internet or a network drive.
The required elements of the function are italicized. More...
HYPERLINK(URL, CHARDATA) NAME(FLDNAME) COLHDG("Column" "Heading" "HYPERLINK")
IMG
This function will build the IMG tag needed to reference an image file
through the SWI. The first must be a varying length character expression,
a literal, or a field name that specifies the location of the image file.
This value will be appended to the current URL and processed by the HTTP
configuration file to locate the image. The second operand specifies an
integer which is the pixel height of the image on the page. More...
IMG(source[,height])
IMGREF
This function is an enhanced version of the IMG function. It allows
for the inclusion of additional attributes for image links such as alt,
width, longdesc, and so on.
More...
IMGREF(source[,height[,optional tags]])
IN
Used in the WHERE clause, the IN operator allows you to find out if
a field matches one of a list of values. More...
IN (subquery)
The subquery IN comparison provides a simple and familiar way of testing
a field or expression result against a list
of values.
More...
INDEX
Characters in the expression are checked against the literal value specified
as a search string. The result is the position of the leftmost character
in the expression that can be found in the search string. More...
INDEX(expression,search string)
INTEGER
Truncate to integer.
More...
INTEGER(expression[,len])
IS NOT NULL
Used in the WHERE clause to test whether a field or expression is not
null.
More...
IS NULL
Used in the WHERE clause to test whether a field or expression is null.
More...
JUSTIFY
Right-justify a character string and include an optional leading pad character.
JUSTIFY(expression, length [,"pad-character"])
LAND
Accepts character value and performs bitwise AND
operation. More...
LAND(expression,expression,expression...)
LEAST
Returns the lowest value from within a list of fields, constants, or
expressions.
More...
LEAST(expression,expression&)
LENGTH
Returns the length of a character field or expression. More...
LENGTH(expression)
LIKE
Search operator (WHERE clause) useful for searching character fields
(and expressions) to determine if they include a specific sequence of
characters. Allows for positional wildcards. More...
LN
This is a base e function. A special logarithm
function to reverse exponentiation and return the root of a number. The
result is a double precision floating point value. The null value is returned
only if the argument is null.
More...
LN(expression)
LNOT
Accepts character value and performs bitwise NOT
operation. More...
LNOT(expression,expression,expression...)
LOG
This is a base 10 function. A special logarithm function to reverse
exponentiation and return the root of a number. The result is a double
precision floating point value. The null value is returned only if the
argument is null.
More...
LOG(expression)
LOR
Accepts character value and performs bitwise OR operation. More...
LOR(expression,expression,expression...)
LOWER
Translates upper case letters in the argument to lower case. More...
LOWER(expression)
LTRIM
Remove specified character
from left side of the expression.
More...
LTRIM(expression,character)
LXOR
Accepts character value and performs bitwise exclusive OR operation.
More...
LXOR(expression,expression,expression...)
MAX (grouping)
Determines the largest value for the specified expression (numeric or
alphanumeric) within the records in the group. The result is null only
if all values in the group are null.
More...
MAX([DISTINCT | ALL ] expression)
MIN (grouping)
Determines the smallest value for the specified expression (numeric
or alphanumeric) within the records in the group. The result is null only
if all values in the group are null.
More...
MIN([DISTINCT | ALL ] expression)
MICROSECOND
Returns the microsecond portion of a timestamp or timestamp duration.
More...
MICROSECOND(expression)
MINUTE
Returns the minute portion of a time, timestamp, time duration, or timestamp duration.
MINUTE(expression)
MMDDYY
Convert a date value to a decimal(6,0) value of the form MMDDYY. Example
MMDDYY(date)
MMDDYYYY
Convert a date value to a decimal(8,0) value of the form MMDDYYYY. Example
MMDDYYYY(date)
MOD
This operator returns the remainder of a division of the first expression
by the second. It is especially useful when you are trying to extract
part of a numeric field. More...
expression MOD expression
MONTH
Returns the month portion of a date, timestamp, date duration, or timestamp
duration.
More...
MONTH(expression)
NODENAME
Returns the relational database name of where a row is located. More...
NODENAME(expression)
NODENUMBER
Returns the node associated with a returned row. More...
NODENUMBER(expression)
NOT
One of four Boolean operators used in the WHERE clause for record selection.
More...
The result of a NOT condition is true if the comparison is false.
OR
One of four Boolean operators used in the WHERE clause for record selection.
More...
The result of an OR condition is true if either one of the comparisons are true.
PARTITION
Returns the partition number of a row obtained by applying the hashing
function on the partitioning key value of the row. More...
PARTITION(expression)
PCTCHG
Compute the percentage change between two numbers. Example
PCTCHG(exp,exp)
POSSTR
Search the source-expression
and return the starting position of the leftmost character of the search-string if it can be found.
If it cannot be found, a zero is returned. The result is an integer. More...
POSSTR(source-expression,search-string)
PROPER
Capitalize the first character of each word in a string, force all others
to lowercase.
Example
PROPER(charexp)
PREV
Return the previous records value.
PREV(expression)
QUARTER
Returns values from 1 to 4 representing quarters of the year for the
given date expression. For example, January through March correspond to
a value of 1.
More...
QUARTER(expression)
RAND
Returns a random number between 0 and 1. More...
RAND(seed-value)
ROUND
Rounds the result to the number of positions specified. (positive or
negative) More...
ROUND(expression[,+/-digits])
RTRIM
Remove specified character
from right side of the expression.
More...
RTRIM(expression,character)
SDEV (grouping)
Statistical standard deviation (n-weighting)
for the values of the expression within the group of records. The result
is null only if all values in the group are null. More...
SDEV([DISTINCT | ALL ] expression)
SECOND
Returns the second portion of a time, timestamp, time duration, or timestamp
duration.
More...
SECOND(expression)
SIGN
Sign of the numeric expression. Returns a 1,0 packed decimal field with
a value of 1 if the expression result is negative, 0 if it is zero, and
+1 if it is positive. The result is null if the expression evaluates to
null.
More...
SIGN(expression)
SIN
Sine of radian argument. The SIN and ASIN functions are inverse operations.
The result is in the range of -1 to 1. More...
SIN(expression)
SINH
Hyperbolic sine of radian argument. More...
SINH(expression)
SOME (subquery)
A simple variation of the basic subquery comparison allows several records
to be included by the subquery. By using the reserved word SOME prior
to the subquery, a set
of values can be compared against a field or expression result, much like
the IN test. More...
SOUNDEX
Returns a 4 character value representing the English sound of the expression.
More...
SOUNDEX(char-expression)
SPLIT
Extract delimited values from a string. Example
SPLIT(charexp, charexp, int) or (charexp, charexp)
SQRT
Returns the square root of the expression as a double precision, floating
point value. The null value is returned only if the argument is null.
More...
SQRT(field)
SST
Use SST or SUBSTR to break a character field or expression into smaller
pieces. Specify the field or expression, the starting position, and the
number of positions. More...
SST(expression,start,length)
STRIP
Alternate to TRIM functions.
Specify type as: (L) leading, (T) trailing, or (B) both. More...
STRIP(expression,type,character)
STRIPX
Remove all occurrences of a given character from a string. Example
STRIPX(charexp, charexp)
SUBSTR
Use SUBSTR or SST to break a character field or expression into smaller
pieces. Specify the field or expression, the starting position, and the
number of positions. More...
SUBSTR(expression,start,length)
SUM (grouping)
Accumulates all the values for the expression across all records in
the group. The result is null only if all values in the group are null.
More...
SUM([DISTINCT | ALL ] expression)
SUM (numeric)
Intra-record sum for a list of fields or expressions. More...
SUM(expression,expression,expression...)
TAN
Tangent of radian argument. The TAN and ATAN functions are inverse operations.
More...
TAN(expression)
TANH
Hyperbolic tangent of radian argument. More...
TANH(expression)
TCAT
CAT and trim blanks at concatenation. More...
TCAT(expression,expression,expression...)
TIME
Creates a time value from the expression argument. More...
TIME(expression)
TIMESTAMP
Creates a timestamp value from either one or two values passed as arguments.
More...
TIMESTAMP(expression [,expression])
TRANSLATE
Characters in the expression
are translated one at a time by searching the from-string.
If the character is found, the corresponding character in the to-string
is substituted. If the to-string is shorter than the from-string and a
corresponding character does not exist, the pad-character
is used as a replacement character. The result is a character string of
the same length as the initial string.
More...
TRANSLATE(expression, [to-string, [from-string, [pad-char] ] ])
TRIM
Remove specified character
from both ends of the expression.
More...
TRIM(expression,character)
UNPACK
Convert a packed or binary value inside a string to a numeric. Example
UNPACK(charexp, int, int)
UPPER
Translate lower case letters in the argument to upper case. The result
is a character string of the same length, with lower case letters (a-z)
converted to upper case (A-Z).
More...
UPPER(expression)
URLSTRING
Convert a string containing invalid URL characters to a canonical URL
form.
Example
URLSTRING(charexp)
VAL2WRD
This UDF converts numeric values into word strings. More...
VAL2WRD(value, 'currency string', 'mask codes')
VALUE
Returns
the first non-null value from within a list of fields, constants, or expressions. More...
VALUE(expression,expression,expression...)
VAR (grouping)
Statistical variance (n-weighting) for the expression values within
the group of records. The result is null only if all values in the group
are null. More...
VAR([DISTINCT | ALL ] expression)
VARCHAR
Creates a varying length field. Specify maximum and allocated length.
More...
VARCHAR(expression, max [,alloc])
VERIFY
Characters in the expression are checked against the literal value specified as a search string. The result is the position of the leftmost character in the expression that can not be found in the search string.
VERIFY(expression,search-string)
WDATA
Creates a hidden result. The named result can be referenced elsewhere
in the Sequel statement, but it is not shown in the output. Dynamic Drill-down options (regroup / show details) are not supported if WDATA is used in a non-*SEQUEL server view. More...
WDATA(expression)
WEEK
Returns values from 1 to 54 representing weeks of the year for the given
date expression. Weeks begin on Sunday and are numbered so that January
1 falls in the first week.
More...
WEEK(expression)
WEEKDAYS
The WEEKDAYS function accepts two dates as input and returns the number
of week days between them. Input values must be date data type.
More...
WEEKDAYS(startdate DATE, enddate DATE)
XLATE
Characters in the expression are translated one at a time using the
translation table specified by the second argument. The translation table
may be qualified or unqualified. If qualified, it must have the form "library/table".
The special value *LIBL is allowed as a library name, and is assumed if
a library name is not specified.
More...
XLATE(expression,translation-table)
XOR
One of four Boolean operators used in the WHERE clause for record selection.
More...
The result of a NOT condition is true if the comparison is false.
YEAR
Returns the year portion of a date, timestamp, date duration, or timestamp
duration.
More...
YEAR(expression)
YYMMDD
Convert a date value to a decimal(6,0) value of the form YYMMDD. Example
YYMMDD(date)
YYYYDDD
Convert a date value to a decimal(7,0) value of the form YYYYDDD. Example
YYYYDDD(date)
YYYYMMDD
Convert a date value to a decimal(8,0) value of the form YYYYMMDD. Example
YYYYMMDD(date)
ZONED
Cast character or numeric expression as fixed decimal field. More...
ZONED(expression[,len[,dec]])