Using the PxPlus SQL ODBC Driver

Scalar Functions

Scalar functions can be included right in the SQL query anywhere a simple term is supported or included through the use of the escape sequence:

scalar function | {fn scalar function}

Where:

scalar function can be any of the String, Numeric, Time and Date or System functions listed below. The following tables list the supported scalar functions with descriptions of their results.

String Functions

String functions are listed below.

Function

Description

ASCII(string)

Integer representing the ASCII code value of the leftmost character of string.

BIT_LENGTH(string)

Length in bits of string.

CHAR(num)

Character that has the ASCII code value specified by num. The value of num should be between 0 and 255.

CHAR_LENGTH(string) or
CHARACTER_LENGTH(
string)

Length in characters of string, if string is of a character data type.

CONCAT(string1, string2)

Character string that is the result of concatenating string2 to string1.

DIFFERENCE(string1, string2)

Integer value that indicates the difference between the values returned by the SOUNDEX function for string1 and string2.

INSERT(string1, start, length, string2)

Character string where length characters have been deleted from string1, beginning at start, and where string2 has been inserted into string1, beginning at start.

LCASE(string)

String equal to that in string, with all uppercase characters converted to lowercase.

LEFT(string, count)

Leftmost count characters of string.

LENGTH(string) or
LEN(string)

Number of characters in string, excluding trailing blanks.

LOCATE(string1, string2[, start])

Starting position of the first occurrence of string1 within string2.

The search for the first occurrence of string1 begins with the first character position in string2 unless the optional argument startis specified.

If start is specified, the search begins with the character position indicated by the value of start. The first character position in string2 is indicated by the value 1.

If string1 is not found within string2, the value 0 is returned.

LTRIM(string)

Characters of string, with leading blanks removed.

OCTET_LENGTH(string)

Returns the length in bytes of string.

POSITION(string1 IN string2)

Position of string1 in string2. The result is an exact numeric with precision of double and a scale of 0.

REPEAT(string, count)

Character string composed of string repeated count times.

REPLACE(string1, string2, string3)

Search string1 for occurrences of string2 and replace with string3.

RIGHT(string, count)

Rightmost count characters of string.

RTRIM(string)

Characters of string with trailing blanks removed.

SOUNDEX(string)

4-digit SOUNDEX code.

SPACE(count)

Character string consisting of count spaces.

SUBSTRING(string, start, length) or
SUBSTR(string, start, length)

Character string that is derived from string, beginning at the character position specified by start for length characters.

UCASE(string)

String equal to that in string, but with all lowercase characters converted to uppercase.

Numeric Functions

Numeric functions are listed below.

Note:
Almost all of the numeric functions accept both numbers 1234 and string numbers '1234' as arguments, with the exceptions being the second arguments for the MOD, POWER, ROUND and TRUNCATE functions, which must be numbers.

 

Function

Description

ABS(num)

Absolute value of num.

ACOS(float)

Arc-cosine of float as an angle expressed in radians.

ASIN(float)

Arc-sine of float as an angle expressed in radians.

ATAN(float)

Arc-tangent of float as an angle expressed in radians.

ATAN2(float1, float2)

Arc-tangent of the x and y coordinates, specified by float1 and float2 respectively as an angle expressed in radians.

CEILING(num)

Smallest integer greater than or equal to num. The return value is of the same data type as the input parameter.

COS(float)

Cosine of float where float is an angle expressed in radians.

COT(float)

Cotangent of float where float is an angle expressed in radians.

DEGREES(num)

Number of degrees converted from num radians.

EXP(float)

Exponential value of float.

FLOOR(num)

Largest integer less than or equal to num. The return value is of the same data type as the input parameter.

LOG(float)

Natural logarithm of float.

LOG10(float)

Base 10 logarithm of float.

MOD(int1, int2)

Remainder (modulus) of int1 divided by int2.

NUM(string) or
NUMBER(
string)

Converts string into a number.

PI( )

Constant value of "pi" as a floating-point value. Pi is defined internally as 3.14159265358979323846264338327950288419716939937510.

POWER(num, int)

Value of num to the power of int.

RADIANS(num)

Number of radians converted from num degrees.

RAND([int])

Random floating-point value using int as the optional seed value.

ROUND(num, int)

Returns num rounded to int places right of the decimal point. If int is negative, num is rounded int places to the left of the decimal point.

SIGN(num)

Returns an indicator of the sign of num.

Where:

If num is less than zero, -1 is returned.
If num equals zero, 0 is returned.
If num is greater than zero, 1 is returned.

SIN(float)

Sine of float where float is an angle expressed in radians.

SQRT(float)

Square root of float.

TAN(float)

Tangent of float where float is an angle expressed in radians.

TRUNCATE(num, int)

Returns num truncated to int places right of the decimal point. If int is negative, num is truncated int places to the left of the decimal point.

Time and Date Functions

Time and Date functions are listed below.

Note:
A date_exp can be in the escaped date format {d '2015-07-28'} or in a simple string format '2015-07-28'.

A time_exp must be in a simple string format '12:00:00'.

A timestamp_exp must be in a simple string format '2015-07-28 12:00:00'.

 

Function

Description

CURRENT_DATE( ) or
CURDATE( )

Current date.

CURRENT_TIME( ) or 
CURTIME( )

Current local time.

CURRENT_TIMESTAMP( )

Current local date and local time as a timestamp value.

DAYNAME(date_exp)

Character string containing the name of the day for the day portion of date_exp. Only long English names are returned (e.g. Monday through Sunday).

DAYOFMONTH(date_exp)

Day of the month based on the month field in date_exp as an integer value in the range of 1 - 31.

DAYOFWEEK(date_exp)

Day of the week based on the week field in date_exp as an integer value in the range of 1 - 7, where 1 represents Sunday.

DAYOFYEAR(date_exp)

Day of the year based on the year field in date_exp as an integer value in the range of 1 - 366.

EXTRACT(extract-field FROM extract-source)

Returns the extract-field portion of the extract-source.

Where:

extract-field

The extract-field argument can be one of the following keywords: YEAR, MONTH, DAY, HOUR, MINUTE or SECOND.

extract-source

The extract-source argument is a date time or interval expression.

The precision of the returned value is implementation defined. The scale is 0 unless SECOND is specified, in which case, the scale is not less than the fractional seconds precision of the extract-source field.

HOUR(time_exp)

Hour based on the hour field in time_exp as an integer value in the range of 0 - 23.

MINUTE(time_exp)

Minute based on the minute field in time_exp as an integer value in the range of 0 - 59.

MONTH(date_exp)

Month based on the month field in date_exp as an integer value in the range of 1 - 12.

MONTHNAME(date_exp)

Character string containing the name of the month for the month portion of date_exp. Only long English names are returned (e.g. January through December).

NOW( )

Current date and time as a timestamp value.

QUARTER(date_exp)

Quarter in date_exp as an integer value in the range of 1 - 4, where 1 represents January 1 through March 31.

SECOND(time_exp)

Second based on the second field in time_exp as an integer value in the range of 0 - 59.

TIMESTAMPADD(interval, integer_exp, timestamp_exp)

Returns the timestamp calculated by adding integer_exp intervals of type interval to timestamp_exp.

Valid values of interval include the following keywords:

SQL_TSI_DAY
SQL_TSI_HOUR
SQL_TSI_MINUTE
SQL_TSI_MONTH
SQL_TSI_QUARTER
SQL_TSI_SECOND
SQL_TSI_WEEK
SQL_TSI_YEAR

Example:

The following SQL statement returns the name of each employee and his/her one year anniversary date:

     SELECT NAME, {fn TIMESTAMPADD(SQL_TSI_YEAR, 1, HIRE_DATE)} FROM EMPLOYEES

If timestamp_exp is a time value and interval specifies days, weeks, months, quarters or years, the date portion of timestamp_exp is set to the current date before calculating the resulting timestamp.

If timestamp_exp is a date value and interval specifies seconds, minutes or hours, the time portion of timestamp_exp is set to 0 before calculating the resulting timestamp.

TIMESTAMPDIFF(interval, timestamp_exp1, timestamp_exp2)

Returns the integer number of intervals of type interval by which timestamp_exp2 is greater than timestamp_exp1.

Valid values of interval include the following keywords:

SQL_TSI_DAY
SQL_TSI_HOUR
SQL_TSI_MINUTE
SQL_TSI_MONTH
SQL_TSI_QUARTER
SQL_TSI_SECOND
SQL_TSI_WEEK
SQL_TSI_YEAR

Example:

The following SQL statement returns the name of each employee and the number of years he/she has been employed:

     SELECT NAME, {fn TIMESTAMPDIFF(SQL_TSI_YEAR, {fn CURDATE( )}, HIRE_DATE)} FROM EMPLOYEES

If either timestamp expression is a time value and interval specifies days, weeks, months, quarters or years, the date portion of that timestamp is set to the current date before calculating the difference between the timestamps.

If either timestamp expression is a date value and interval specifies seconds, minutes or hours, the time portion of that timestamp is set to 0 before calculating the difference between the timestamps.

WEEK(date_exp)

Week of the year based on the week field in date_exp as an integer value in the range of 1-53.

YEAR(date_exp)

Year based on the year field in date_exp as an integer value.

System Functions

System functions are listed below.

Function

Description

CASE expression
WHEN
expression THEN expression [.n]
[ELSE
expression] 
END

Simple Case function. Evaluate a list of WHEN expressions for equality with the CASE expression and return the related result expression.

If no WHEN expression is equal to the CASE expression, return the ELSE expression if present. If there is no ELSE expression, then return NULL.

CASE
WHEN
boolean THEN expression [.n]
[ELSE
expression] 
END

Searched Case function. Evaluate a list of Booleans and return the related result expression of the first Boolean in the list that is true.

If no WHEN expression is equal to the CASE expression, return the ELSE expression if present. If there is no ELSE expression, then return NULL.

CAST(expression AS data_type [( integer1 [, integer2] )] )

Convert expression into data_type. Optionally specify the length or precision or precision and scale of the converted to data_type. (Added in PxPlus 2016)

For details on supported data types, see Data Types below.

CONVERT(expression, data_type)

Convert expression into data_type.

For details on supported data types, see Data Types below.

DATABASE( )

Return the current Data Source Name (DSN) description text.

IFNULL(expression1, expression2)

If expression1 evaluates to NULL, then the result of expression2 is returned; otherwise, the result of expression1 is returned.

Data Types

Data types are listed below.

Data Type

Description

SQL_BIGINT
BIGINT

Exact numeric value with precision 19 (if signed) or 20 (if unsigned) and scale 0.

SQL_BINARY [(length)]
BINARY [(length)]

Binary data of fixed length. The length can be optionally overridden using the CAST function. Binary data is returned as two characters per byte Hex representation.

Example:

"hello world" is "68656C6C6F20776F726C64".

SQL_VARBINARY [(length)]
VARBINARY [(length)]

Variable length binary data. The maximum length can be optionally overridden using the CAST function. Binary data is returned as two characters per byte Hex representation.

Example:

"hello world" is "68656C6C6F20776F726C64".

SQL_BIT
BIT

Single bit binary data; i.e. 0 or 1.

SQL_CHAR [(length)]
CHAR [(length)]
CHARACTER [(length)]

Character string of fixed length. The length can be optionally overridden using the CAST function.

SQL_VARCHAR [(length)]
VARCHAR [(length)]
CHAR VARYING [(length)]
CHARACTER VARYING [(length)]

Variable length character string. The maximum length can be optionally overridden using the CAST function.

SQL_DATE
SQL_TYPE_DATE
DATE

A calendar date in the format YYYYMMDD.

SQL_DECIMAL [(precision[, scale])]
DECIMAL [(precision[, scale])]
DEC [(precision[, scale])]

Signed, exact, numeric value with a precision of 15 and scale of 3. The precision and/or scale can be optionally overridden using the CAST function.

SQL_DOUBLE
DOUBLE
DOUBLE PRECISION

Signed, approximate, numeric value with a precision of 15.

SQL_FLOAT [(precision)]
FLOAT [(precision)]

Signed, approximate, numeric value with precision of 15. The precision can be optionally overridden using the CAST function.

SQL_INTEGER
INTEGER
INT

Exact numeric value with precision 10 and scale 0.

SQL_NUMERIC [(precision[, scale])]
NUMERIC [(precision[, scale])]

Signed, exact, numeric value with a precision of 15 and scale of 3. The precision and/or scale can be optionally overridden using the CAST function.

SQL_REAL
REAL

Signed, approximate, numeric value with a precision of 7.

SQL_SMALLINT
SMALLINT

Exact numeric value with precision of 5 and scale of 0.

SQL_TINYINT
TINYINT

Exact numeric value with precision of 3 and scale of 0.