Following are the symbols (operators) available in Teradata SQL environment that are used to calculate mathematical operations.

** — Exponentiation: Return the result of a number rose to a power (exponent). Use the double asterisk character (**) to separate the number from the power; for example, SELECT 2**3, will result in 8. This is a Teradata extension to the ANSI SQL-99 standard.

* — Used to Multiply; SELECT 2*3, would result in 6.

/ — Used to Divide; SELECT 2/3, would result in .66666667 but the SELECT statement has to be written as follows

SELECT cast (2 as decimal (18, 8))/cast (3 as decimal (18, 8));

MOD –- MOD or modulo calculates the remainder in a division operation. For example, SELECT 3 mod 2 would result in 1. This is also a Teradata extension to the ANSI SQL-99 standard.

+ — For Addition; SELECT 2+3, would give result as 5.

-  – For Subtraction; SELECT 3-2, would give 1.

+ — Also used for Unary plus (positive value); SELECT +3, would give the value 3.

-  – Also used Unary minus (negative value); SELECT -3, would display the value ‘-3’.

The order of precedence or evaluation for arithmetic operators is as follows;

In all cases, SQL performs expressions enclosed in parentheses first. The following table lists the order in which arithmetic expressions are performed when no parentheses are present. Operators of the same precedence are evaluated from left to right. Here follows the order of precedence with numbering where 1 represents the highest and 2 as second highest so on and so forth.

1. Unary plus

2. Unary minus

3. Exponentiation

4. Multiplication

5. Division

6. MOD operator

7. Addition

8. Subtraction

Now let’s look at some arithmetic functions present in Teradata SQL:

• ABS  – Stands for ‘ABSOLUTE’ and it computes the absolute value of an argument passed into it. For example, SELECT ABS (-6), would result in just the number 6, without the minus sign. Remember that it is not the same as rounding off. To round off a particular decimal value you can make use the FORMAT function described earlier. SELECT 123.566(FORMAT ‘999.99’), would display (in BTEQ session) the value 123.57.

• EXP — Raises the base of natural logarithms (e) to the power of the argument, where e = 2.71828182845905. SELECT EXP (2) would result in 7.38905609893065E 000.

• LN — Computes the natural logarithm of the argument. SELECT LN (7.38905609893065E 000) would result in   2.00000000000000E 000.

• LOG – This function computes the base 10 logarithm of an argument. For example SELECT LOG (20) would give the following result;

Log (20)

———————-

1.30102999566398E 000

• NULLIFZERO – If you want to avoid errors with division by zero, use this function which converts data from zero to null hence you would not get ‘2618:  Invalid calculation:  division by zero’ error. SELECT 5/ NULLIFZERO (0), would not result in any error.

• RANDOM — Returns a random integer number for each row of the results table from a given lower limit and an upper limit. SELECT RANDOM (1,100), would result in random values from 1 to 100.

• SQRT – This function is used to compute the square root of an argument. For Example SELECT SQRT (9) would display the value 3.

• ZEROIFNULL – This function is used where in case the value of a column might return a NULL but we still need to do an arithmetic calculation and avoid any error. Following is the syntax;

SELECT ZEROIFNULL (argument);

As mentioned earlier also you might not be able to see the correct output If you are using Teradata SQL Assistant, you would not see all the formatting displayed. USE BTEQ instead to see proper output.

Our Random Articles

More Links