SQL Functions

Functions can be used in the following SQL statements:

Select
Insert
Update
Delete
While
If

Scalar functions take single-valued expressions as their argument. Aggregate functions take a set of values (for example, the contents of a column in a table) as their argument. Aggregate functions cannot be used in if or while statements.

Scalar Functions

There are seven types of scalar functions:

Data type conversion
Numeric
String
Date
Bit-wise
Hash
Random number

The scalar functions require either one or two single-value arguments. Scalar functions can be nested to any level.

Note that if II_DECIMAL is set to comma, be sure that when SQL syntax requires a comma (such as a list of table columns or SQL 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.) Type conversions can also be specified using the CAST expression.

Name	Operand Type	Result Type	Description
ansidate(expr)	c, text, char, varchar, ingresdate, ansidate, timestamp with time zone, timestamp without time zone, timestamp with local time zone	ansidate	Converts a c, char, varchar text, ingresdate, ansidate, or timestamp type to internal ansidate representation.
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) or ingresdate(expr)	c, text, char, varchar	ingresdate	Converts a c, char, varchar or text string to internal date representation.
decimal(expr [,precision[,scale]])	any except ingresdate and ANSI date/time	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)	ingresdate	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)	any	varchar	Returns the hexadecimal representation of the internal Ingres form of the argument expression. The length of the result is twice the length of the argument, because the hexadecimal equivalent of each byte of the argument requires two bytes. For example, hex('ABC') returns '414243' (ASCII) or 'C1C2C3' (EBCDIC). Also, hex(int4(125)) returns '0000007D', the hexadecimal equivalent of the 4 byte binary integer 125.
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 occurs 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 occurs 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 occurs if the integer portion of a floating point or decimal value is too large to be returned in the requested format.
int8(expr)	c, char, varchar, text, float, money, decimal, integer1, smallint, integer	integer8	Converts the specified expression to integer. Decimal and floating point values are truncated. Numeric overflow occurs if the integer portion of a floating point or decimal value is too large to be returned in the requested format.
interval_dtos(expr)	c, text, char, varchar, ingresdate, interval day to second	interval day to second	Converts c, text, char, varchar, ingresdate or interval year to month types to internal interval day to second representation.
interval_ytom(expr)	c, text, char, varchar, ingresdate, interval year to month	interval year to month	Converts c, text, char, varchar, ingresdate or interval year to month types to internal interval year to month representation.
long_byte (expr)	any	long byte	Converts the expression to long byte binary data.
long_varchar (expr)	c, char, varchar, text, long varchar, long byte	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.
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.
time_local(expr)	c, text, char, varchar, ingresdate, time without time zone, time with time zone, time with local time zone, timestamp without time zone, timestamp with time zone, timestamp with local time zone	time with local timezone	Converts a c, char, varchar text, ingresdate, time, or timestamp type to internal time with local time zone representation.
time_with_tz(expr)	c, text, char, varchar, ingresdate, time without time zone, time with time zone, time with local time zone, timestamp without time zone, timestamp with time zone, timestamp with local time zone	time with time zone	Converts a c, char, varchar text, ingresdate, time, or timestamp types to internal time with time zone representation.
time(expr) or time_wo_tz(expr)	c, text, char, varchar, ingresdate, time without time zone, time with time zone, time with local time zone, timestamp without time zone, timestamp with time zone, timestamp with local time zone	time without timezone	Converts a c, char, varchar text, ingresdate, time, or timestamp type to internal time without time zone representation.
timestamp_local(expr)	c, text, char, varchar, ingresdate, ansidate, time without time zone, time with time zone, time with local time zone, timestamp without time zone, timestamp with time zone, timestamp with local time zone	timestamp with local time zone	Converts a c, char, varchar text, ingresdate, ansidate, time, or timestamp type to internal time with local time zone representation.
timestamp_with_tz(expr)	c, text, char, varchar, ingresdate, time without time zone, time with time zone, time with local time zone, timestamp without time zone, timestamp with time zone, timestamp with local time zone	timestamp with timezone	Converts a c, char, varchar text, ingresdate, ansidate, time, or timestamp type to internal timestamp with time zone representation.
timestamp(expr) or timestamp_wo_tz(expr)	c, text, char, varchar, ingresdate, ansidate, time without time zone, time with time zone, time with local time zone, timestamp without time zone, timestamp with time zone, timestamp with local time zone	timestamp without time zone	Converts a c, char, varchar text, ingresdate, ansidate, time, or timestamp type to internal timestamp without time zone representation.
unhex(expr)	varchar, c, text	varchar	Returns the opposite of the hex function. For example, unhex('61626320') returns 'abc' and unhex('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: Normally 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, 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 of Argument	Result Length
byte	Length of operand
byte varying	Length of operand
c	Length of operand
char	Length of operand
decimal	Depends on precision and scale of column
float & float4	11 characters; 12 characters on IEEE computers
ingresdate	25 characters
integer1 (smallint)	6 characters
integer	6 characters
integer4	13 characters
long varbyte	Length of operand
long varchar	Length of operand
money	20 characters
text	Length of operand
varchar	Length of operand

Numeric Functions

SQL 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 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
Position
Shift
Squeeze
Substring
Trim
ANSI 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 2008 characters or bytes, the result is truncated to 2008 characters or bytes.

String Functions Supported in SQL

The following table lists the string functions supported in SQL. The expressions c1 and c2, representing function arguments, can be any of the string types (char, varchar, long varchar, c, text, byte, varbyte, long varbyte) or any of the Unicode types (nchar, nvarchar, long nvarchar), except where noted. The expressions len, n, n1, n2 or nshift, representing function arguments, are the integer type. For string functions operating on one of the string types, the integer arguments represent character (or 8-bit octet) counts or offsets. For string functions operating on one of the Unicode types, the integer arguments represent "code point" (or 16-bit Unicode characters) counts or offsets.

Name	Result Type	Description
charextract(c1,n)	char or nchar	Returns the nth byte or code point of c1. If n is larger than the length of the string, the result is a blank character. It does not support long varchar or long nvarchar arguments.
collation_weight(c1 [,n1])	varbyte	Returns the collation weight of any char, c, varchar, text, nchar, or nvarchar value c1. n1 is an optional collation ID when the collation weight is desired relative to a specific collation.
concat(c1,c2)	any character or Unicode 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. This function does not support long nvarchar arguments.
left(c1,len)	any character or Unicode 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. This function does not support long nvarchar arguments.
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 within 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. This function does not support long varchar or long nvarchar arguments.
lowercase(c1) or lower(c1)	any character or Unicode data type	Converts all upper case characters in c1 to lower case. This function does not support long nvarchar arguments.
pad(c1)	text, varchar, or nvarchar	Returns c1 with trailing blanks appended to c1; for instance, if c1 is a varchar string that can hold fifty characters but only has two characters, pad(c1) appends 48 trailing blanks to c1 to form the result. This function does not support long varchar or long nvarchar arguments.
position (c1 IN c2)	smallint	ANSI compliant version of locate function.
right(c1,len)	any character or Unicode 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. This function does not support long nvarchar arguments.
shift(c1,nshift)	any character or Unicode 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. This function does not support long varchar or long nvarchar arguments.
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. This function does not support long varchar or any Unicode arguments.
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 variablelength character string data type (not fixed-length character data type). The result is the same length as the argument. This function does not support long varchar or long nvarchar arguments.
substring(c1 from loc[FOR len])	varchar or nvarchar	Returns part of c1 starting at the loc position and either extending to the end of the string or for the number of characters/code points in the len operand. The result format is a varchar or nvarchar the size of c1. This function does not support long varchar or long nvarchar arguments.
trim(c1)	text or varchar	Returns c1 without trailing blanks. The result has the same length as c1. This function does not support long varchar or long nvarchar arguments.
trim([[trim_spec] [c1] FROM] c2)	any character string variable	ANSI compliant version of the trim function. The trim_spec is LEADING, TRAILING, or BOTH, indicating that the result will return c2 with all occurrences of c1 removed from the beginning, end, or both. In the absence of trim_spec, BOTH is assumed. In the absence of c1, the space is assumed.
notrim(c1)	any character string variable	Retains trailing blanks when placing a value in a varchar column. This function can be used only in an embedded SQL program. For more information, see the Embedded SQL Companion Guide.
uppercase(c1) or upper(c1)	any character data type	Converts all lower case characters in c1 to upper case. This function does not support long nvarchar arguments.

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
nchar	nchar	No	No	nchar
nchar	nvarchar	No	No	nchar
nvarchar	nchar	No	No	nchar
nvarchar	nvarchar	No	No	nvarchar
Byte	Byte	No	No	Byte
Byte	Varbyte	No	No	Byte
Varbyte	Byte	No	No	Byte
Varbyte	Varbyte	No	No	Varbyte
Byte	Longbyte	No	No	Longbyte
Varbyte	Longbyte	No	No	Longbyte
Longbyte	Longbyte	No	No	Longbyte

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

The following date functions extract the specified portion of a date or timestamp. They can be used with the ansidate and ingresdate data types.

year()
Extracts the year portion of a date or timestamp.
quarter()
Extracts the quarter corresponding to the date or timestamp. Quarters are numbered 1 through 4.
month()
Extracts the month portion of a date or timestamp.
week()
Extracts the number of the week of the year that the date or timestamp refers to. 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. Weeks are numbered 1 to 53.
week_iso()
Extracts the number of the week of the year that the date or timestamp refers to, and conforms to ISO 8601 definition for number of the week. Week_iso begin on Monday, but the first week is the week that has the first Thursday of the year. 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.
day()
Extracts the day portion of a date or timestamp.
hour()
Extracts the hour portion of a date or timestamp.
minute()
Extracts the minute portion of a date or timestamp.
second()
Extracts the second portion of a date or timestamp.
microsecond()
Extracts the microsecond portion of a date or timestamp.

Examples:

day('2006-12-15 12:30:55.1234') returns 15

second('2006-12-15 12:30:55.1234') returns 55.1234

Date Functions for Ingresdate Data Type

Note: The functions described in this section apply only to the ingresdate data type and not to the ANSI date/time data types (date, time, timestamp, interval). The effect of the ingresdate functions can be derived by first coercing the ANSI date/time value to ingresdate using the ingresdate() function, then executing the desired ingresdate function.

SQL 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.

Some date functions require specifying of 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, ses, 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 following table lists the date functions:

Name	Format (Result)	Description
date_trunc(unit,date)	date	Returns a date value truncated to the specified unit.
date_part(unit,date)	integer	Returns an integer containing the specified (unit) component of the input date.
date_gmt(date)	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. For example, the query: select date_gmt('1-1-98 10:13 PM PST') returns the following value: 1998_01_01 06:13:00 GMT while the query: select date_gmt('1-1-1998') returns: 1998_01_01 00:00:00 GMT
gmt_timestamp(s)	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'. For example, the query: select (gmt_timestamp (1234567890)) returns the following value: 2009_02_13 23:31:30 GMT while the query: (II_TIMEZONE_NAME = AUSTRALIA-QUEENSLAND) select date(gmt_timestamp (1234567890)) returns: 14-feb-2009 09:31:30
interval (unit,date_interval)	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. For example, the query: select(interval('days', '5 years')) returns the following value: 1826.213
_date(s)	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. For example, the query: select _date(123456) returns the following value: 2-jan-70 Note that this function formats a leading space for day values less than 10.
_date4(s)	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. For example, with II_DATE_FORMAT set to US, the query: select _date4(123456) returns the following value: 02-jan-1970 while with II_DATE_FORMAT set to MULTINATIONAL, the query: select _date4(123456) returns this value: 02/01/1970
_time(s)	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). For example, the query: select _time(123456) returns the following value: 02:17

Truncate Dates using date_trunc Function

Use the date_trunc function to group all the dates within 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 falls into the previous year.

Using Date_part

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

Extract Function—Extract a Field from a Date/Time Value

The EXTRACT function can be used in an SQL statement to extract a particular field from date/time value.

The syntax for EXTRACT is as follows:

EXTRACT (component FROM extract_source)

component
Is the field you want to extract. Valid values are:

YEAR

MONTH

DAY

HOUR

MINUTE

SECOND

TIMEZONE_HOUR

TIMEZONE_MINUTE
extract_source
Is any date/time or interval value expression.

Examples:

SELECT EXTRACT (YEAR FROM datecol) FROM datetable;

SELECT EXTRACT (MONTH FROM CURRENT_TIME);

SELECT EXTRACT (HOUR FROM timecol) FROM tab1 \g

SELECT * FROM tx WHERE EXTRACT(HOUR FROM tab_time) = 17 \g

Bit-wise Functions

Bit-wise functions operate from right to left, with shorter operands padded with hex 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.

There are six external bit-wise functions:

bit_add—The logical "add" of two byte operands; any overflow is disregarded.
bit_and—The logical "and" of two byte operands. For example, if two bits are 1, the answer is 1, otherwise the answer is 0.
bit_not—The logical "not" of two byte operands.
bit_or—The logical "or" of two byte operands. For example, if either or both bits are 1, the answer is 1.
bit_xor—The logical "xor" of two byte operands. For example, if either bit is 1, the answer is 1.
intextract (byte,int)—Similar to charextract. Returns the nth byte of b1 as an integer. If n is larger than b1, 0 is returned. Extracts the number at the given location. For example, if int is < 1 or > length(byte) à 0, i4 is returned.

Hash Functions

The hash 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: Because 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.

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 enhance the "randomness" of the returned value. Note that the seed value can be any integer.

If you omit the value, Ingres multiplies the process ID by the number of seconds past 1/1/1970 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 within the specified range (that is, l >= x <= h).
randomf(l,h)— Passing two integer values generates an integer result within the specified range; passing two floats generates a float within the specified range; passing an int and a float causes them to be coerced to an int and generates an integer result within the specified range (that is, l >= x <= h).

Aggregate Functions

Aggregate functions include the following:

Unary
Binary
Count

Unary Aggregate Functions

A unary aggregate function returns a single value based on the contents of a column. Aggregate functions are also called set functions.

Note: For OpenROAD users, aggregate functions used within OpenROAD can only be coded inside SQL statements.

The following example uses the sum aggregate function to calculate the total of salaries for employees in department 23:

select sum (employee.salary)
from employee
where employee.dept = 23;

SQL Aggregate Functions

The following table lists SQL aggregate functions:

Name	Result Data Type	Description
any	integer	Returns 1 if any row in the table fulfills the where clause, or 0 if no rows fulfill the where clause.
avg	float, money, date (interval only)	Average (sum/count) The sum of the values must be within the range of the result data type.
count	integer	Count of non-null occurrences
max	same as argument	Maximum value
min	same as argument	Minimum value
sum	integer, float, money, date (interval only)	Column total
stddev_pop	float	Compute the population form of the standard deviation (square root of the population variance of the group).
stddev_samp	float	Computes the sample form of the standard deviation (square root of the sample variance of the group).
var_pop	float	Computes the population form of the variance (sum of the squares of the difference of each argument value in the group from the mean of the values, divided by the count of the values).
var_samp	float	Computes the sample form of the variance (sum of the squares of the difference of each argument value in the group from the mean of the values, divided by the count of the values minus 1).

The general syntax of an aggregate function is as follows:

function_name ([distinct | all] expr)

where function_name denotes an aggregate function and expr denotes any expression that does not include an aggregate function reference (at any level of nesting).

To eliminate duplicate values, specify distinct. To retain duplicate values, specify all (this is the default.) Distinct is not meaningful with the functions min and max, because these functions return single values (and not a set of values).

Nulls are ignored by the aggregate functions, with the exception of count, as described in Count(*) Function.

Binary Aggregate Functions

Ingres supports a variety of binary aggregate functions that perform a variety of regression and correlation analysis. For all of the binary aggregate functions, the first argument is the independent variable and the second argument is the dependent variable.

The following table lists binary aggregate functions:

Name	Result Data Type	Description
regr_count (indep_parm, dep_parm)	integer	Count of rows with non-null values for both dependent and independent variables.
covar_pop (indep_parm, dep_parm)	float	Population covariance (sum of the products of the difference of the independent variable from its mean, times the difference of the dependent variable from its mean, divided by the number of rows).
covar_samp (indep_parm, dep_parm)	float	Sample covariance (sum of the products of the difference of the independent variable from its mean, times the difference of the dependent variable from its mean, divided by the number of rows minus 1).
corr (indep_parm, dep_parm)	float	Correlation coefficient (ratio of the population covariance divided by the product of the population standard deviation of the independent variable and the population standard deviation of the dependent variable).
regr_r2 (indep_parm, dep_parm)	float	Square of the correlation coefficient.
regr_slope (indep_parm, dep_parm)	float	Slope of the least-squares-fit linear equation determined by the (independent variable, dependent variable) pairs.
regr_intercept (indep_parm, dep_parm)	float	Y-intercept of the least-squares-fit linear equation determined by the (independent variable, dependent variable) pairs.
regr_sxx (indep_parm, dep_parm)	float	Sum of the squares of the independent variable.
regr_syy (indep_parm, dep_parm)	float	Sum of the squares of the dependent variable.
regr_sxy (indep_parm, dep_parm)	float	Sum of the product of the independent variable and the dependent variable.
regr_avgx (indep_parm, dep_parm)	float	Average of the independent variables.
regr_avgy (indep_parm, dep_parm)	float	Average of the dependent variables.

Count(*) Function

The count function can take the wildcard character, *, as an argument. This character is used to count the number of rows in a result table, including rows that contain nulls. For example, the statement:

select count(*)
from employee
where dept = 23;

counts the number of employees in department 23. The asterisk (*) argument cannot be qualified with all or distinct.

Because count(*) counts rows rather than columns, count(*) does not ignore nulls. Consider the following table:

Name	Exemptions
Smith	0
Jones	2
Tanghetti	4
Fong	Null
Stevens	Null

Running:

count(exemptions)

returns the value of 3, whereas:

count(*)

returns 5.

Except count, if the argument to an aggregate function evaluates to an empty set, the function returns a null. The count function returns a zero.

Aggregate Functions and Decimal Data

Given decimal arguments, aggregate functions (with the exception of count) return decimal results.

The following table explains how to determine the scale and precision of results returned for aggregates with decimal arguments:

Name	Precision of Result	Scale of Result
count	Not applicable	Not applicable
sum	31	Same as argument
avg	31	Scale of argument + 1 (to a maximum of 31)
max	Same as argument	Same as argument
min	Same as argument	Same as argument

Group By Clause with Aggregate Functions

The group by clause allows aggregate functions to be performed on subsets of the rows in the table. The subsets are defined by the group by clause. For example, the following statement selects rows from a table of political candidates, groups the rows by party, and returns the name of each party and the average funding for the candidates in that party.

select party, avg(funding)
from candidates
group by party;

Restrictions on the Use of Aggregate Functions

The following restrictions apply to the use of aggregate functions:

Aggregate functions cannot be nested.
Aggregate functions can only be used in select or having clauses.
If a select or having clause contains an aggregate function, columns not specified in the aggregate must be specified in the group by clause. For example:
select dept, avg(emp_age)
from employee
group by dept;

The above select statement specifies two columns, dept and emp_age, but only emp_age is referenced by the aggregate function, avg. The dept column is specified in the group by clause.

Ifnull Function

The ifnull function specifies a value other than a null that is returned to your application when a null is encountered. The ifnull function is specified as follows:

ifnull(v1,v2)

If the value of the first argument is not null, ifnull returns the value of the first argument. If the first argument evaluates to a null, ifnull returns the second argument.

For example, the sum, avg, max, and min aggregate functions return a null if the argument to the function evaluates to an empty set. To receive a value instead of a null when the function evaluates to an empty set, use the ifnull function, as in this example:

ifnull(sum(employee.salary)/25, -1)

Ifnull returns the value of the expression sum(employee.salary)/25 unless that expression is null. If the expression is null, the ifnull function returns -1.

Note: If an attempt is made to use the ifnull function with data types that are not nullable, such as system_maintained logical keys, a runtime error is returned.

Note: If II_DECIMAL is set to comma, be sure that when SQL syntax requires a comma (such as a list of table columns or SQL functions with several parameters), that the comma is followed by a space. For example:

select col1, ifnull(col2, 0), left(col4, 22) from t1:

Ifnull Result Data Types

If the arguments are of the same data type, the result is of that data type. If the two arguments are of different data types, they must be of comparable data types. For a description of comparable data types, see Assignment.

When the arguments are of different but comparable data types, the DBMS Server uses the following rules to determine the data type of the result:

The result type is always the higher of the two data types; the order of precedence of the data types is as follows:
date > money > float4 > float > decimal > integer > smallint > integer1

and

c > text > char > varchar > long varchar > byte > byte varying > long byte
The result length is taken from the longest value. For example:
ifnull (varchar (5), c10)

results in c10.

The result is nullable if either argument is nullable. The first argument is not required to be nullable, though in most applications it is nullable.

Ifnull and Decimal Data

If both arguments are decimal, the data type of the result returned by ifnull is decimal, and the precision (total number of digits) and scale (number of digits to the right of the decimal point) of the result is determined as follows:

Precision—The largest number of digits to the left of the decimal point (precision - scale) plus largest scale (to a maximum of 31)
Scale—The largest scale

Universal Unique Identifier (UUID)

A Universal Unique Identifier (UUID) is a 128 bit, unique identifier generated by the local system. It is unique across both space and time with respect to the space of all UUIDs.

Benefits of Using a UUID

No centralized authority is responsible for assigning UUIDs. They can be generated on demand (10 million per second per machine if needed).

A UUID can be used for multiple purposes:

Tagging objects that have a brief life
Reliably identifying persistent objects across a network
Assigning as unique values to transactions as transaction IDs in a distributed system

UUIDs are fixed-sized (128-bits), which is small relative to other alternatives. This fixed small size lends itself well to sorting, ordering, and hashing of all sorts, sorting in databases, simple allocation, and ease of programming.

UUID Format

The basic format of a unique 128-bits (16 octets) UUID:

Field	Data Type	Octet Number	Note
time_low	unsigned 32 bit integer	0-3	The low field of the timestamp.
time_mid	unsigned 16 bit integer	4-5	Time middle field of the timestamp.
time_hi_and_version	unsigned 16 bit integer	6-7	The high field of the timestamp multiplex with the release number.
clock_seq_hi_and_reserved	unsigned 8 bit integer	8	The high field of the clock sequence multiplex with the variant.
clock_seq_low	unsigned 8 bit integer	9	The low field of the clock sequence.
node	unsigned 48 bit integer	10-15	The spatially unique node identifier.

SQL Functions for UUID Implementation

Ingres implements the following SQL functions to create, convert and compare UUIDs:

uuid_create ( );
uuid_from_char (c);
uuid_compare(uuid1, uuid2);

uuid_compare(uuid1, uuid2) Function

The uuid_compare(uuid1, uuid2) function, upon completion, returns an integer value of:

Return	Meaning
(-1)	uuid1 < uuid2
( 0 )	uuid1 == uuid2
(+1)	uuid1 > uuid2

* select uuid_compare(u1,u2) from uuidtable\g

P,,,,T
.col1
5,,,,6
. 1.
F,,,,G

uuid_from_char(c) Function

The uuid_from_char(c) function converts a generated UUID from character representation into byte representation:

//
// Inserts a generated UUID in character format.
//

* insert into uuidtochar values ();\g

* select * from uuidtochar;\g

P,,,,,,,,,,,,,T
.c1
5,,,,,,,,,,,,,6
.f703c440-b35c-01d5-8637-00805fc13ce5.
F,,,,,,,,,,,,,G

//
// converts UUID into byte representation
//
* select uuid_from_char (u1) from uuidtochar;\g

P,,,,,,T
.col1
5,,,,,,6
.œ\003Ä@³\\\001Õ\2067\221\0134¡\221\013.
F,,,,,,G

uuid_create ( ) Function

The uuid_create() function creates a 128 bit UUID:

> createdb uuiddb
> sql uuiddb
* create table uuidtable (u1 byte (16), u2 byte(16)); \g
* insert into uuidtable values (uuid_create(), uuid_create())\g
//
// verify length in byte format
//
* select length(u1) from uuidtable;\g

P,,,,T
.col1
5,,,,6
. 16.
F,,,,G

Length returned equals 16 bytes.

UUID Usage

A UUID can be used to tag records to ensure that the database records are uniquely identified regardless of which database they are stored in, for example, in a system where there are two separate physical databases containing accounting data from two different physical locations.