Math functions

IBM® Netezza® provides math functions.

Trigonometric functions

The following table describes the trigonometric functions provided by IBM Netezza.

Table 1. Trigonometric functions
Function	Input data type	Output data type	Description
`acos(x)`	double precision	double precision	Inverse cosine.
`asin(x)`	double precision	double precision	Inverse sine.
`atan(x)`	double precision	double precision	Inverse tangent.
`atan2(x,y)`	double precision	double precision	Inverse tangent of x/y.
`cos(x)`	double precision	double precision	Cosine.
`cot(x)`	double precision	double precision	Cotangent.
`degrees(x)`	double precision	double precision	Convert radians to degrees.
`pi()`	(none)	double precision	Pi (ratio of circumference to diameter).
`radians(x)`	double precision	double precision	Convert degrees to radians.
`sin(x)`	double precision	double precision	Sine.
`tan(x)`	double precision	double precision	Tangent.

Random number functions

The following table describes the random number functions provided by IBM Netezza.

Table 2. Random number math functions
Function	Input data type	Output data type	Description	Example	Result
`random()`	(none)	double precision	Random value greater than 0 and less than 1.	`random()`	`0.27116098407244`
`setseed(x)`		(none)	Set the seed for the `random()` function.	`setseed(0.548)`	(return code)

Numeric functions

The following table describes numeric functions provided by IBM Netezza.

Table 3. Numeric functions
Function	Input data type	Output data type	Description	Example	Result
`n!`	byteint, smallint, integer, bigint	int8	Factorial of the integer `n`: `n×(n-1)×...×1`	`5!`	`120`
`abs(x)`	byteint, smallint, integer, bigint, double precision, real, numeric	same as `x`	Absolute value.	`abs (-17.4)`	`17.4`
`ceil(x)`	numeric	numeric	Smallest whole number that is not less than `x`	`ceil (-42.8)`	`-42`
`dceil(x)`	double precision	double precision	Smallest whole number that is not less than `x`	`dceil (42.8)`	`43`
`dfloor(x)`	double precision	double precision	Largest integer not greater than argument	`floor(42.8)`	`42`
`exp(x)`	double precision, numeric	same as `x`	Exponential.	`exp(1.0)`	`2.71828182845905`
`floor(x)`	numeric	numeric	largest integer not greater than argument	`floor(-42.8)`	`-43`
`fpow(a,b)`	real	real	a raised to the power of b	`pow(9.0, 3.0)`	`729`
`ln(x)`	double precision	double	natural logarithm	`ln(2.0)`	`0.693147180559945`
`log(x)`	numeric	numeric	base 10 logarithm	`log(100.0)`	`2`
`mod(x,y)`	double precision, real, numeric, byteint, smallint, integer, bigint	If the type of either x or y is double precision or a real, the output type is double precision; otherwise, if either x or y is numeric, the output is numeric; otherwise, x and y are integers and the output data type is the wider of the two input data types	Computes the remainder of x/y	`mod(9,4)`	`1`
`numeric_sqrt(x)`	numeric	numeric	square root	`numeric_sqrt(2)`	`1.4142`
`pow(a,b)`	double precision	dp	a raised to the power of b	`pow(9.0, 3.0)`	`729`
`round(x)`	double precision, numeric	same as input	round to nearest integer	`round(42.4)`	`42`
`round(v,n)`	v is numeric, n is byteint, smallint, integer, bigint	numeric	round to s decimal places	`round(2.4382,2)`	`2.44`
`sign(x)`	numeric	numeric	sign of the argument(-1,0,+1)	`sign(-8.4)`	`-1`
`sqrt(x)`	double precision	double	square root	`sqrt(2.0)`	`1.4142135623731`
`trunc(x)`	double precision, numeric	same as input	truncate toward zero	`trunc(42.8)`	`42`
`trunc(v,n)`	v is numeric, n is byteint, smallint, integer, bigint	numeric	truncate to s decimal places	`trunc(2.4382,2)`	`2.43`

Note: The result of the pow function must fit in a FLOAT8 (be a double precision floating point value). A FLOAT8 can hold positive or negative values with a magnitude as large as 1.798 x 10³⁰⁸ and as small as 4.941 x 10^-324(approximately).

Binary math functions

The following table describes the binary math functions provided by IBM Netezza.

Table 4. Binary math functions
Function	Description	Example	Result
`intNand(x,y)`	Bitwise AND of `x` and `y`.	`int2and(3,6)`	2 (smallint)
`intNor(x,y)`	Bitwise OR of `x` and `y`.	`int1or(3,6)`	7 (byteint)
`intNxor(x,y)`	Bitwise exclusive OR of `x` and `y`.	`int8xor(3,6)`	5 (bigint)
`intNnot(x)`	Bitwise NOT of `x`.	`int4xor(3)`	-4 (integer)
`intNshl(x,y [,z])`	Bitwise AND of `x` and `z` (if specified), then shift left by `y` bits (shift left, with optional mask).	`int2shl(3,1,6)`	4 (smallint)
`intNshr(x,y [,z])`	Bitwise AND of `x` and `z` (if specified), then shift right by `y` bits (shift right, with optional mask).	`int2shr(3,1,6)`	1 (smallint)

In each function, the placeholder N represents the size of the integer data type that the function operates on and returns:

1: byteint (alias int1)
2: smallint (alias int2)
4: integer (alias int or int4)
8: bigint (alias int8)