ABS function (ODBC compatible)
Syntax
ABS ( expression )
Description
The scalar function ABS computes the absolute value of expression.
Example
SELECT ABS (MONTHS_BETWEEN (SYSDATE, order_date))
FROM orders
WHERE ABS (MONTHS_BETWEEN (SYSDATE, order_date)) > 3 ;
Notes
- The argument to the function must be of type TINYINT, SMALLINT, INTEGER, NUMERIC, REAL or FLOAT.
- The result is of type NUMERIC.
- If the argument expression evaluates to null, the result is null.
ACOS function (ODBC compatible)
Syntax
ACOS ( expression )
Description
The scalar function ACOS returns the arccosine of expression.
Example
select acos (.5) 'Arccosine in radians' from syscalctable;
ARCCOSINE IN RAD
----------------
1.047197551196598
1 record selected
select acos (.5) * (180/ pi()) 'Arccosine in degrees' from syscalctable;
ARCCOSINE IN DEG
----------------
59.999999999999993
1 record selected
Notes
ACOS takes the ratio (expression) of two sides of a right triangle and returns the corresponding angle. The ratio is the length of the side adjacent to the angle divided by the length of the hypotenuse.
The result is expressed in radians and is in the range -Pi/2 to Pi/2 radians. To convert degrees to radians, multiply degrees by Pi/180. To convert radians to degrees, multiply radians by 180/Pi.
- Expression must be in the range -1 to 1.
- Expression must evaluate to an approximate numeric data type.
ASIN function (ODBC compatible)
Syntax
ASIN ( expression )
Description
The scalar function ASIN returns the arcsine of expression.
Example
select asin (1) * (180/ pi()) 'Arcsine in degrees' from syscalctable;
ARCSINE IN DEGRE
----------------
90.000000000000000
1 record selected
select asin (1) 'Arcsine in radians' from syscalctable;
ARCSINE IN RADIA
----------------
1.570796326794897
1 record selected
Notes
ASIN takes the ratio (expression) of two sides of a right triangle and returns the corresponding angle. The ratio is the length of the side opposite the angle divided by the length of the hypotenuse.
The result is expressed in radians and is in the range -Pi/2 to Pi/2 radians. To convert degrees to radians, multiply degrees by Pi/180. To convert radians to degrees, multiply radians by 180/Pi.
- Expression must be in the range -1 to 1.
- Expression must evaluate to an approximate numeric data type.
ATAN function (ODBC compatible)
Syntax
ATAN ( expression )
Description
The scalar function ATAN returns the arctangent of expression.
Example
select atan (1) * (180/ pi()) 'Arctangent in degrees' from syscalctable;
ARCTANGENT IN DE
----------------
45.000000000000000
1 record selected
select atan (1) 'Arctangent in radians' from syscalctable;
ARCTANGENT IN RA
----------------
0.785398163397448
1 record selected
Notes
ATAN takes the ratio (expression) of two sides of a right triangle and returns the corresponding angle. The ratio is the length of the side opposite the angle divided by the length of the side adjacent to the angle.
The result is expressed in radians and is in the range -Pi/2 to Pi/2 radians. To convert degrees to radians, multiply degrees by Pi/180. To convert radians to degrees, multiply radians by 180/Pi.
- Expression must be in the range -1 to 1.
- Expression must evaluate to an approximate numeric data type.
ATAN2 function (ODBC compatible)
Syntax
ATAN2 ( expression1 , expression2 )
Description
The scalar function ATAN2 returns the arctangent of the x and y coordinates specified by expression1 and expression2.
Example
select atan2 (1,1) * (180/ pi()) 'Arctangent in degrees' from syscalctable;
ARCTANGENT IN DE
----------------
45.000000000000000
1 record selected
select atan2 (1,1) 'Arctangent in radians' from syscalctable;
ARCTANGENT IN RA
----------------
0.785398163397448
1 record selected
Notes
ATAN2 takes the ratio of two sides of a right triangle and returns the corresponding angle. The ratio is the length of the side opposite the angle divided by the length of the side adjacent to the angle.
expression1 and expression2 specify the x and y coordinates of the end of the hypotenuse opposite the angle.
The result is expressed in radians and is in the range -Pi/2 to Pi/2 radians. To convert degrees to radians, multiply degrees by Pi/180. To convert radians to degrees, multiply radians by 180/Pi.
Both expression1 and expression2 must evaluate to approximate numeric data types.
COS function (ODBC compatible)
Syntax
COS ( expression )
Description
The scalar function COS returns the cosine of expression.
Example
select cos(45 * pi()/180) 'Cosine of 45 degrees' from syscalctable;
COSINE OF 45 DEG
----------------
0.707106781186548
1 record selected
Notes
COS takes an angle (expression) and returns the ratio of two sides of a right triangle. The ratio is the length of the side adjacent to the angle divided by the length of the hypotenuse.
- Expression specifies an angle in radians
- Expression must evaluate to an approximate numeric data type.
To convert degrees to radians, multiply degrees by Pi/180. To convert radians to degrees, multiply radians by 180/Pi.
COT SQL Scalar Function
Syntax
COT(expression)
Description
The scalar function COT returns the cotangent of the expression. COT takes an angle expression and returns the ratio of two sides of a right angle triangle. The ratio is the length of the side adjacent to the angle to the length of the side opposite to the angle.
Example
SELECT COT(45 * PI()/180) 'COT OF 45 DEGREES' from admin.syscalctable;
COT OF 45 DEGREES
------------------
1.000000000000000
1 record selected
Details
Expression specifies an angle in radians. Expression must evaluate to an approximate numeric data type. To convert degrees to radians, multiply degrees by PI/180. To convert radians to degrees, multiply radians by 180/PI.
Note: ODBC compatible
CEILING function (ODBC compatible)
Syntax
CEILING ( expression )
Description
The scalar function CEILING returns the smallest integer greater than or equal to expression.
Example
SELECT CEILING (32.5) 'Ceiling'
FROM ADMIN.SYSCALCTABLE;
Notes
- Expression must evaluate to a numeric data type.
DEGREES function (ODBC compatible)
Syntax
DEGREES ( expression )
Description
The scalar function DEGREES returns the number of degrees in an angle specified in radians by expression.
Example
SELECT DEGREES(3.14159265359) 'Degrees in pi Radians'
FROM ADMIN.SYSCALCTABLE;
Notes
- Expression specifies an angle in radians.
- Expression must evaluate to a numeric data type.
EXP function (ODBC compatible)
Syntax
EXP ( expression )
Description
The scalar function EXP returns the exponential value of expression (e raised to the power of expression).
Example
SELECT EXP(1) FROM SYSCALCTABLE;
2.718282
--------
2.718281828459046
1 record selected
Notes
- Expression must evaluate to an approximate numeric data type.
FLOOR function (ODBC compatible)
Syntax
FLOOR ( expression )
Description
The scalar function FLOOR returns the largest integer less than or equal to expression.
Example
SELECT FLOOR (32.5) 'Floor'
FROM ADMIN.SYSCALCTABLE;
Notes
- Expression must evaluate to a numeric data type.
ISNUMERIC function
Syntax
ISNUMERIC( expr )
Description
The scalar function ISNUMERIC returns 1 if the input expression evaluates to an exact numeric or approximate numeric type; otherwise it returns 0. A return value of 1 guarantees that expr can be converted to one of these numeric types.
Arguments
- expr - Is an expression to be evaluated.
Example
SELECT ISNUMERIC(id), ISNUMERIC(tbl) FROM systables
WHERE tbl = 'systables';
ISNUMERIC(ID ISNUMERIC(TB
------------ ------------
1 0
LOG SQL Scalar Function
Syntax
LOG ( expression )
Description
The scalar function LOG returns the natural logarithm of expression.
Example
SELECT LOG( 100 ) 'Natural Logarithm of 100' FROM
admin.syscalctable ;
NATURAL LOGARITH
----------------
4.605170185988092
1 record selected
Details
Expression must evaluate to an approximate numeric data type.
Note: ODBC compatible
LOG10 function (ODBC compatible)
Syntax
LOG10 ( expression )
Description
The scalar function LOG10 returns the base 10 logarithm of expression.
Example
SELECT LOG10 (100) 'Log base 10 of 100'
FROM ADMIN.SYSCALCTABLE;
Notes
- Expression must evaluate to an approximate numeric data type.
MOD function (ODBC compatible)
Syntax
MOD ( expression1, expression2 )
Description
The scalar function MOD returns the remainder of expression1 divided by expression2.
Example
SELECT MOD (11, 4) 'Modulus'
FROM ADMIN.SYSCALCTABLE;
Notes
- Both expression1 and expression2 must evaluate to exact numeric data types.
- If expression2 evaluates to zero, MOD returns zero.
PI function (ODBC compatible)
Syntax
PI ( )
Description
The scalar function PI returns the constant value of pi as a floating point value.
Example
SELECT PI ()
FROM ADMIN.SYSCALCTABLE;
POWER function (ODBC compatible)
Syntax
POWER ( expression1 , expression2 )
Description
The scalar function POWER returns expression1 raised to the power of expression2.
Example
SELECT POWER ( 3 , 2) '3 raised to the 2nd power'
FROM ADMIN.SYSCALCTABLE;
Notes
- Expression1 must evaluate to a numeric data type.
- Expression2 must evaluate to an exact numeric data type.
RADIANS function (ODBC compatible)
Syntax
RADIANS ( expression )
Description
The scalar function RADIANS returns the number of radians in an angle specified in degrees by expression.
Example
SELECT RADIANS(180) 'Radians in 180 degrees'
FROM ADMIN.SYSCALCTABLE;
Notes
- Expression specifies an angle in degrees.
- Expression must evaluate to a numeric data type.
RAND function (ODBC compatible)
Syntax
RAND ( [ expression ] )
Description
The scalar function RAND returns a randomly-generated number, using expression as an optional seed value.
Example
SELECT RAND(3) 'Random number using 3 as seed value'
FROM ADMIN.SYSCALCTABLE;
Notes
- Expression must evaluate to an exact numeric data type.
ROUND function
Syntax
ROUND( number, length [,operation_type] )
Description
The scalar function rounds number to length decimal places.
If value of length is positive, number is rounded to the number of decimal places specified by length.
If length is negative, number is rounded on the left side of the decimal point, as specified by the positive value of length.
Arguments
- number - This is an expression of an exact numeric or approximate numeric data type.
- length - This is the precision or number of digits to which number is to be rounded. The argument value must be of data type TINYINT, SMALLINT or INTEGER.
- operation_type - This is the type of operation - rounding or truncation - to perform. The argument value must be of type TINYINT, SMALLINT or INTEGER. If the argument is omitted or has a value of 0 (default), number is rounded. If a value other than 0 is specified, number is truncated.
Example
SELECT price, ROUND(price, 1), ROUND(price, -1)
FROM custorder;
PRICE ROUND(PRI ROUND(PRI
----- --------- ---------
1546.56 1546.60 1550.00
SELECT price, ROUND(price, 1, 1), ROUND(price, -1, 1)
FROM custorder;
PRICE ROUND(PRI ROUND(PRI
----- --------- ---------
1546.56 1546.50 1540.00
ROWID (extension)
Syntax
ROWID
Description
The scalar function ROWID returns the row identifier of the current row in a table. This function takes no arguments. The ROWID of a row is determined when the row is inserted into the table. Once assigned, the ROWID remains the same for the row until the row is deleted. At any given time, each row in a table is uniquely identified by its ROWID.
Selecting a row in a table using its ROWID is the most efficient way of selecting the row. For example:
SELECT *
FROM customers
WHERE ROWID = '10';
SIGN function (ODBC compatible)
Syntax
SIGN ( expression )
Description
The scalar function SIGN returns 1 if expression is positive, -1 if expression is negative, or zero if it is zero.
Example
SELECT SIGN(-14) 'Sign'
FROM ADMIN.SYSCALCTABLE;
Notes
- Expression must evaluate to a numeric data type.
SIN function (ODBC compatible)
Syntax
SIN ( expression )
Description
The scalar function SIN returns the sine of expression.
Example
select sin(45 * pi()/180) 'Sine of 45 degrees' from syscalctable;
SINE OF 45 DEGRE
----------------
0.707106781186547
1 record selected
Notes
SIN takes an angle (expression) and returns the ratio of two sides of a right triangle. The ratio is the length of the side opposite the angle divided by the length of the hypotenuse.
- Expression specifies an angle in radians.
- Expression must evaluate to an approximate numeric data type.
To convert degrees to radians, multiply degrees by Pi/180. To convert radians to degrees, multiply radians by 180/Pi.
SQRT function (ODBC compatible)
Syntax
SQRT ( expression )
Description
The scalar function SQRT returns the square root of the expression.
Example
SELECT SQRT(28) 'square root of 28'
FROM ADMIN.SYSCALCTABLE;
Notes
- The value of the expression must be positive.
- The expression must evaluate to an approximate numeric data type.
TAN function (ODBC compatible)
Syntax
TAN ( expression )
Description
The scalar function TAN returns the tangent of expression.
Example
select tan(45 * pi()/180) 'Tangent of 45 degrees' from syscalctable;
TANGENT OF 45 DE
----------------
1.000000000000000
1 record selected
Notes
TAN takes an angle (expression) and returns the ratio of two sides of a right triangle. The ratio is the length of the side opposite the angle divided by the length of the side adjacent to the angle.
- Expression specifies an angle in radians.
- Expression must evaluate to an approximate numeric data type.
To convert degrees to radians, multiply degrees by Pi/180. To convert radians to degrees, multiply radians by 180/Pi.