Was this helpful?
Scalar Functions
There are six types of scalar functions:
Data type conversion
Numeric
String
Date
Bitwise
Random number
The scalar functions require either one or more single-value arguments. In most instances, scalar functions can be nested to any level. Certain restrictions apply when using some Enterprise Access products. For details, see the documentation provided with your Enterprise Access product.
Note:  If II_DECIMAL is set to comma, be sure that when OpenSQL syntax requires a comma (such as a list of table columns or OpenSQL functions with several parameters), that the comma is followed by a space.
For example:
Select col1, ifnull(col2, 0), left(col4, 22) from t1:
Data Type Conversion Functions
The following table lists the data type conversion functions. (When converting decimal values to strings, the length of the result depends on the precision and scale of the decimal column.)
Name
Operand Type
Result Type
Description
byte(expr [, len])
any
byte
Converts the expression to byte binary data. If the optional length argument is specified, the function returns the leftmost len bytes. Len must be a positive integer value that does not exceed the length of the expr argument.
c(expr [, len])
any
c
Converts argument to c string. If the optional length argument is specified, the function returns the leftmost len characters. Len must be a positive integer value that does not exceed the length of the expr string.
char(expr [, len])
any
char
Converts argument to char string. If the optional length argument is specified, the function returns the leftmost len characters. Len must be a positive integer value that does not exceed the length of the expr string.
date(expr)
c, text, char, varchar
date
Converts a c, char, varchar or text string to internal date representation.
decimal(expr [,precision[,scale]])
any except date
decimal
Converts any numeric expression to a decimal value. If scale (number of decimal digits) is omitted, the scale of the result is 0. If precision (total number of digits) is omitted, the precision of the result is determined by the data type of the operand, as follows:
Operand Default
Datatype Precision
smallint 5
integer1 5
integer 11
float 15
float4 15
decimal 15
money 15
Decimal overflow occurs if the result contains more digits to the left of the decimal point than the specified or default precision and scale can accommodate.
dow(expr)
date
c
Converts an absolute date into its day of week (for example, 'Mon,' 'Tue'). The result length is 3.
float4(expr)
c, char, varchar, text, float, money, decimal, integer1, smallint, integer
float4
Converts the specified expression to float4.
float8(expr)
c, char, varchar, text, float, money, decimal, integer1, smallint, integer
float
Converts the specified expression to float.
hex(expr)
varchar, char, c, text
varchar
Returns the hexadecimal representation of the argument string. The length of the result is twice the length of the argument, because the hexadecimal equivalent of each character requires two bytes. For example, hex('A') returns '61' (ASCII) or 'C1' (EBCDIC).
int1(expr)
c, char, varchar, text, float, money, decimal, integer1, smallint, integer
integer1
Converts the specified expression to integer1. Decimal and floating-point values are truncated. Numeric overflow will occur if the integer portion of a floating-point or decimal value is too large to be returned in the requested format.
int2(expr)
c, char, varchar, text, float, money, decimal, integer1, smallint, integer
smallint
Converts the specified expression to smallint. Decimal and floating-point values are truncated. Numeric overflow will occur if the integer portion of a floating-point or decimal value is too large to be returned in the requested format.
int4(expr)
c, char, varchar, text, float, money, decimal, integer1, smallint, integer
integer
Converts the specified expression to integer. Decimal and floating-point values are truncated. Numeric overflow will occur if the integer portion of a floating-point or decimal value is too large to be returned in the requested format.
long_byte
(expr)
any
long byte
Converts the expression to long byte binary data.
long_varchar (expr)
any
long varchar
Converts the expression to a long varchar.
money(expr)
c, char, varchar,
text, float, money, decimal,
integer1,
smallint,
integer
money
Converts the specified expression to internal money representation. Rounds floating-point and decimal values, if necessary.
nchar(expr [, len])
any
nchar
Converts argument to nchar unicode string. If the optional length argument is specified, the function returns the leftmost len characters. Len must be a positive integer value that does not exceed the length of the expr string.
nvarchar(expr [, len])
any
nvarchar
Converts argument to nvarchar unicode string. If the optional length argument is specified, the function returns the leftmost len characters. Len must be a positive integer value that does not exceed the length of the expr string.
long_varchar (expr)
c, char, varchar, text, long varchar, long byte
long varchar
Converts the expression to a long varchar.
object_key(expr)
varchar, char, c, text
object_key
Converts the operand to an object_key.
table_key(expr)
varchar, char, c, text
table_key
Converts the operand to a table_key.
text(expr [, len])
any
text
Converts argument to text string. If the optional length argument is specified, the function returns the leftmost len characters. Len must be a positive integer value that does not exceed the length of the expr string.
unhex(expr)
varchar, c, text
varbyte
Returns the opposite of the hex function. For example, unhex(x'61626320') returns 'abc' and unhex(x'01204161') returns '\001Aa'.
Exceptions can occur when a "c" data type suppresses the display of certain stored characters, or when the output data type differs from the input type.
Note:  The result must be CAST to the desired data type as not all contexts support the display or handling of BYTE and VARBYTE data.
 
 
 
Typically one character is generated for every two hex digits being converted to a printable character. If the hex digit pair being converted does not translate to a printable character, then the value is converted to a backslash (\), followed by the numeric value of the hex digit pair as a three-digit octal value.
varbyte(expr [, len])
any
byte varying
Converts the expression to byte varying binary data. If the optional length argument is specified, the function returns the leftmost len bytes. Len must be a positive integer value that does not exceed the length of the expr argument.
varchar(expr [, len])
any
varchar
Converts argument to varchar string. If the optional length argument is specified, the function returns the leftmost len characters. Len must be a positive integer value that does not exceed the length of the expr string.
If the optional length parameter is omitted, the length of the result returned by the data type conversion functions c(), char(), varchar(), and text() are as follows:
Data Type or Argument
Result Length
byte
Length of operand
byte varying
Length of operand
c
Length of operand
char
Length of operand
date
25 characters
decimal
Depends on precision and scale of column
float & float4
11 characters; 12 characters on IEEE computers
integer1 (smallint)
6 characters
integer
6 characters
integer4
13 characters
long byte
Length of operand
long varchar
Length of operand
money
20 characters
text
Length of operand
varchar
Length of operand
Numeric Functions
OpenSQL supports the numeric functions listed in the following table:
Name
Operand Type
Result Type
Description
abs(n)
all numeric types and money
same as n
Absolute value of n.
atan(n)
all numeric types
float
Arctangent of n; returns a value from (-pi/2) to pi/2.
cos(n)
all numeric types
float
Cosine of n; returns a value from -1 to 1.
exp(n)
all numeric types and money
float
Exponential of n.
log(n)
ln(n)
all numeric types and money
float
Natural logarithm of n.
mod(n,b)
integer,
smallint,
integer1, decimal
same as b
n modulo b. The result is the same data type as b.
Decimal values are truncated.
power(x,y)
all numeric types
float
x to the power of y (identical to x ** y)
sin(n)
all numeric types
float
Sine of n; returns a value from -1 to 1.
sqrt(n)
all numeric types and money
float
Square root of n.
For trigonometric functions (atan(), cos(), and sin()), specify arguments in radians. To convert degrees to radians, use the following formula:
radians = degrees/360 * 2 * pi
To obtain a tangent, divide sin() by cos().
String Functions
String functions perform a variety of operations on character data. String functions can be nested. For example:
left(right(x.name, size(x.name) - 1), 3)
returns the substring of x.name from character positions 2 through 4, and
concat(concat(x.lastname, ', '), x.firstname)
concatenates x.lastname with a comma and then concatenates x.firstname with the first concatenation result. The + operator can also be used to concatenate strings:
x.lastname + ', ' + x.firstname
The following string functions do not accept long varchar or long byte columns:
Locate
Pad
Shift
Squeeze
Trim
Notrim
Charextract
To apply any of the preceding functions to a long varchar or long byte column, first coerce the column to an acceptable data type. For example:
squeeze(varchar(long_varchar_column))
If a coercion function is applied to a long varchar or long byte value that is longer than 1928 characters or bytes, the result is truncated to 1928 characters or bytes.
The following table lists the string functions supported in OpenSQL. The expressions c1 and c2, representing the arguments, can be any of the string types, except where noted. The expressions len and nshift represent integer arguments.
Name
Result Type
Description
charextract(c1,n)
varchar
Returns the nth byte of c1. If n is larger than the length of the string, then the result is a blank character.
concat(c1,c2)
any character data type, byte
Concatenates one string to another. The result size is the sum of the sizes of the two arguments. If the result is a c or char string, it is padded with blanks to achieve the proper length. To determine the data type results of concatenating strings, see the table regarding results of string concatenation.
left(c1,len)
any character data type
Returns the leftmost len characters of c1. If the result is a fixed-length c or char string, it is the same length as c1, padded with blanks. The result format is the same as c1.
length(c1)
smallint
(for long varchar, returns 4-byte integer)
If c1 is a fixed-length c or char string, returns the length of c1 without trailing blanks. If c1 is a variable-length string, returns the number of characters actually in c1.
locate(c1,c2)
smallint
Returns the location of the first occurrence of c2 in c1, including trailing blanks from c2. The location is in the range 1 to size(c1). If c2 is not found, the function returns size(c1) + 1. The function size() is described below, in this table.
If c1 and c2 are different string data types, c2 is coerced into the c1 data type.
lowercase(c1)
or lower(c1)
any character or Unicode data type
Converts all upper case characters in c1 to lower case.
pad(c1)
text or varchar
Returns c1 with trailing blanks appended to c1; for instance, if c1 is a varchar string that could hold fifty characters but only has two characters, then pad(c1) appends 48 trailing blanks to c1 to form the result.
right(c1,len)
any character data type
Returns the rightmost len characters of c1. Trailing blanks are not removed first. If c1 is a fixed-length character string, the result is padded to the same length as c1. If c1 is a variable-length character string, no padding occurs. The result format is the same as c1.
shift(c1,nshift)
any character data type
Shifts the string nshift places to the right if nshift > 0 and to the left if nshift < 0. If c1 is a fixed-length character string, the result is padded with blanks to the length of c1. If c1 is a variable-length character string, no padding occurs. The result format is the same as c1.
size(c1)
smallint
Returns the declared size of c1 without removal of trailing blanks.
soundex(c1)
any character data type
Returns a c1 four-character field that can be used to find similar sounding strings. For example, SMITH and SMYTHE produce the same soundex code. If there are less than three characters, the result is padded by trailing zero(s). If there are more than three characters, the result is achieved by dropping the rightmost digit(s).
This function is useful for finding like-sounding strings quickly. A list of similar sounding strings can be shown in a search list rather than just the next strings in the index.
squeeze(c1)
text or varchar
Compresses white space. White space is defined as any sequence of blanks, null characters, newlines (line feeds), carriage returns, horizontal tabs and form feeds (vertical tabs). Trims white space from the beginning and end of the string, and replaces all other white space with single blanks.
This function is useful for comparisons. The value for c1 must be a string of variable‑length character string data type (not fixed-length character data type). The result is the same length as the argument.
substring(c1 from n1 [for n2])
varchar or nvarchar
Returns a substring of parameter c1 starting at offset n1. If n2 is specified, the resulting string is min(n2, length(c1)-n1) in length. If n1 is 0 or negative, the resulting substring starts with the 1st byte of c1. If n1 > length(c1), the resulting string has length 0. If n2 is negative, an error is returned.
trim(c1)
text or varchar
Returns c1 without trailing blanks. The result has the same length as c1.
notrim(c1)
any character string variable
Retains trailing blanks when placing a value in a varchar column. This function can only be used in an embedded OpenSQL program. For more information, see the Embedded SQL Companion Guide.
uppercase(c1)
or upper(c1)
any character or Unicode data type
Converts all lower case characters in c1 to upper case.
String Concatenation Results
The following table shows the results of concatenating expressions of various character data types:
1st String
2nd String
Trim Blanks
 
Result Type
from 1st?
from 2nd?
c
c
Yes
--
c
c
text
Yes
--
c
c
char
Yes
--
c
c
varchar
Yes
--
c
c
long varchar
Yes
No
long varchar
text
c
No
--
c
char
c
Yes
--
c
varchar
c
No
--
c
long varchar
c
No
No
long varchar
text
text
No
No
text
text
char
No
Yes
text
text
varchar
No
No
text
text
long varchar
No
No
long varchar
char
text
Yes
No
text
varchar
text
No
No
text
long varchar
text
No
No
long varchar
char
char
No
--
char
char
varchar
No
--
char
char
long varchar
No
No
long varchar
varchar
char
No
--
char
long varchar
char
No
No
long varchar
varchar
varchar
No
No
varchar
long varchar
long varchar
No
No
long varchar
When concatenating more than two operands, expressions are evaluated from left to right. For example:
varchar + char + varchar
is evaluated as:
(varchar+char)+varchar
To control concatenation results for strings with trailing blanks, use the trim, notrim, and pad functions.
Date Functions
OpenSQL supports functions that derive values from absolute dates and from interval dates. These functions operate on columns that contain date values. An additional function, dow(), returns the day of the week (mon, tue, and so on) for a specified date. For a description of the dow() function, see Data Type Conversion Functions.
Some date functions require you to specify a unit parameter; unit parameters must be specified using a quoted string. The following table lists valid unit parameters:
Date Portion
How Specified
Second
second, seconds, sec, secs
Minute
minute, minutes, min, mins
Hour
hour, hours, hr, hrs
Day
day, days
Week
week, weeks, wk, wks
ISO-Week
iso-week, iso-wk
Month
month, months, mo, mos
Quarter
quarter, quarters, qtr, qtrs
Year
year, years, yr, yrs
The date functions are as follows:
DATE_TRUNC
date_trunc(unit,date)
Result Type: date
Returns a date value truncated to the specified unit.
DATE_PART
date_part(unit,date)
Result Type:integer
Returns an integer containing the specified (unit) component of the input date.
DATE_GMT
date_gmt(date)
Result Type: Any character data type
Converts an absolute date into the Greenwich Mean Time character equivalent with the format yyyy_mm_dd hh:mm:ss GMT. If the absolute date does not include a time, the time portion of the result is returned as 00:00:00.
select date_gmt('1-1-98 10:13 PM PST')
returns 1998_01_01 06:13:00 GMT, while
select date_gmt('1-1-1998')
returns 1998_01_01 00:00:00 GMT
GMT_TIMESTAMP
gmt_timestamp(s)
Result Type: Any character data type
Returns a twenty-three-character string giving the date s seconds after January 1, 1970 GMT. The output format is 'yyyy_mm_dd hh:mm:ss GMT'.
select (gmt_timestamp (1234567890))
returns the following value 2009_02_13 23:31:30 GMT.
With II_TIMEZONE_NAME = AUSTRALIA_BRISBANE, the query
select date(gmt_timestamp (1234567890))
returns 14-feb-2009 09:31:30
INTERVAL
interval (unit,date_interval)
Result Type: float
Converts a date interval into a floating-point constant expressed in the unit of measurement specified by unit. The interval function assumes that there are 30.436875 days per month and 365.2425 days per year when using the mos, qtrs, and yrs specifications.
select(interval('days', '5 years'))
returns 1826.213
This function is not supported for the Oracle and MS SQL Enterprise Access products.
_DATE
_date(s)
Result Type:Any character data type
Returns a nine-character string giving the date s seconds after January 1, 1970 GMT. The output format is dd-mmm-yy.
select _date(123456)
returns 2-jan-70
_DATE4
_date4(s)
Result Type: Any character data type
Returns an eleven-character string giving the date s seconds after January 1, 1970 GMT. The output format is controlled by the II_DATE_FORMAT setting.
With II_DATE_FORMAT set to US, the query:
select _date4(123456)
returns 02-jan-1970
while with II_DATE_FORMAT set to MULTINATIONAL, the query:
select _date4(123456)
returns 02/01/1970
_TIME
_time(s)
Result Type: Any character data type
Returns a five-character string giving the time s seconds after January 1, 1970 GMT. The output format is hh:mm (seconds are truncated).
select _time(123456)
returns 02:17
Date_trunc Function
Use the date_trunc function to group all the dates in the same month or year, and so forth. For example:
date_trunc('month',date('23-oct-1998 12:33'))
returns 1-oct-1998, and
date_trunc('year',date('23-oct-1998'))
returns 1-jan-1998.
Truncation takes place in terms of calendar years and quarters (1-jan, 1-apr, 1-jun, and 1-oct).
To truncate in terms of a fiscal year, offset the calendar date by the number of months between the beginning of your fiscal year and the beginning of the next calendar year (6 mos for a fiscal year beginning July 1, or 4 mos for a fiscal year beginning September 1):
date_trunc('year',date+'4 mos') - '4 mos'
Weeks start on Monday. The beginning of a week for an early January date may fall into the previous year.
Date_part Function
This function is useful in set functions and in assuring correct ordering in complex date manipulation. For example, if date_field contains the value 23-oct-1998, then:
date_part('month',date(date_field))
returns a value of 10 (representing October), and
date_part('day',date(date_field))
returns a value of 23.
Months are numbered 1 to 12, starting with January.
Hours are returned according to the 24-hour clock.
Quarters are numbered 1 through 4.
Week 1 begins on the first Monday of the year. Dates before the first Monday of the year are considered to be in week 0. However, if you specify ISO-Week, which is ISO 8601 compliant, the week begins on Monday, but the first week is the week that has the first Thursday. The weeks are numbered 1 through 53.
Therefore, if you are using Week and the date falls before the first Monday in the current year, date_part returns 0. If you are using ISO-Week and the date falls before the week containing the first Thursday of the year, that date is considered part of the last week of the previous year, and date_part returns either 52 or 53.
The following table illustrates the difference between Week and ISO-Week:
Date Column
Day of Week
Week
ISO-Week
02-jan-1998
Fri
0
1
04-jan-1998
Sun
0
1
02-jan-1999
Sat
0
53
04-jan-1999
Mon
1
1
02-jan-2000
Sun
0
52
04-jan-2000
Tue
1
1
02-jan-2001
Tue
1
1
04-jan-2001
Thu
1
1
Bitwise Functions
Bitwise functions operate from right to left, with shorter operands padded with binary zeroes to the left. Each result is a byte field the size of the longer operand, except BIT_NOT, which takes a single byte operand and returns the same-sized operand.
The external bitwise functions are:
BIT_ADD
Returns the logical "add" of two byte operands; any overflow is disregarded.
"Logical add" is adding the binary content of the two operands as if they were unsigned binary integers.
If the sum does not fit in the result field, the overflow is disregarded and the rest is retained. For example, the result of adding 200 and 90 would be 290, but 290 does not fit in one byte (maximum value is 255), so the overflow is disregarded and the logical add result is 34 (290 minus 256).
BIT_AND
Returns the logical "and" of two byte operands. If two bits are 1, the answer is 1; otherwise the answer is 0.
BIT_NOT
Returns the logical "not" of a single byte operand.
BIT_OR
Returns the logical "or" of two byte operands. If either or both bits are 1, the answer is 1.
BIT_XOR
Returns the logical "xor" of two byte operands. If either, but not both, bits is 1, the answer is 1; otherwise the answer is 0.
INTEXTRACT(byte, n)
Similar to the CHAREXTRACT string function. Returns the nth byte as an integer. For example: intextract(X'0A020C04', 3) returns 12, which is the integer value for the third byte '0C'.
n is an integer field. If n is less than 1 or larger than the number of bytes available, then a 0 is returned. For example: intextract(X'0A020C04', -2) or intextract(X'0A020C04', 5) both return 0.
Hash Function
This function is used to generate a four-byte numeric value from expressions of all data types except long data types. Note that the implicit size for the expression can affect the result. For example:
select hash(1), hash(int1(1)), hash(int2(1)), hash(int4(1))\g
returns the following single row:
Col1
Col2
Col3
Col4
-920527466
1526341860
-920527466
-1447292811
Note:  Since the constant 1 is implicitly a short integer, only the return values for Hash(1) and Hash(int2(1)) match. For the remaining columns, the difference in the number of bytes holding the integer leads to a different hash value. Also note that the generated hash value is not guaranteed unique, even if the input values are unique. The hash function is not supported for Enterprise Access products.
Random Number Functions
The random number function is used to generate random values. Use the following statement to set the beginning value for the random functions:
[exec sql] set random_seed [value]
There is a global seed value and local seed values. The global value is used until you issue “set random_seed,” which changes the value of the local seed. Once changed, the local seed is used for the whole session. If you are using the global seed value, the seed is changed whenever a random function executes. This means that other users issuing random calls will enhance the “randomness” of the returned value. Note that the seed value can be any integer.
If you omit the value, then Ingres multiplies the process ID by the number of seconds past 1/1/2016 until now. This value generates a random starting point. You can use value to run a regression test from a static start and get identical results.
There are four random number functions:
random() - Returns a random integer based on a seed value.
randomf() - Returns a random float based on a seed value between 0 and 1. This is slower than random, but produces a more random number.
random(l,h) - Returns a random integer in the specified range
(that is, l >= x <= h).
randomf(l,h) - Passing two integer values generates an integer result in the specified range; passing two floats generates a float in the specified range; passing an int and a float causes them to be coerced to an int and generates an integer result in the specified range (that is, l >= x <= h).
Last modified date: 08/29/2024