** — 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.
Popularity: 35% [?]
Our Random Articles
- New Year Wishes 2010
- New Year Wishes
- How to use EXTRACT function with date and time columns
- How to Post on a Separate/New Page in WordPress
- Usage of Macro in Teradata
More Links
7 Comments
Can anyone tell me What is the default order of precedence of the logical operators AND, OR, NOT ?? Thanks in advance
Hi Vaideesh, Here are the rules for Order of Evaluation
A. If an expression contains more than one of the same operator, the evaluation precedence is left to right.
B. If an expression contains a combination of logical operators, the order of evaluation is as follows:
1 NOT
2 AND
3 OR
C. Parentheses can be used to establish the desired evaluation precedence.
D. The logical expressions in a conditional expression are not always evaluated left to right.
Just to add, one should avoid using a conditional expression if its accuracy depends on the order in
which its logical expressions are evaluated. For example, compare the following two expressions:
Field2/(NULLIF(Field1,0))> 112
Field1 0 AND Field2/Field1 > 112
The first expression guarantees exclusion of division by zero. The second allows the possibility of error, because the order of its evaluation determines the exclusion of zeros.
Hope this helps
Hi!
how can I generate random integers (with decimal places) between 0 and 1 through SELECT RANDOM (0,1)
just had the same problem stringbeans…. already got any answer about this?
Hi,
While I know that you can “cast” the result of RANDOM fucntion to float or decimal and then divide it by 10 or more but I am not sure about the syntax, but here is the logic,
SELECT CAST (RANDOM ( 0, 1) as DECIMAL/FLOAT)/10 FORMAT (’9.99′) AS RANDOM_NBR_DCML
Hope that helps.
I am creating a table in Teradata SQL. One of the fields (DEVICE_ID_NUMBER) is numeric, but is too large to delare as Integer (resulting in an overflow).
Hence, I am declaring it as Float, which works:
CREATE TABLE Table1 ,FALLBACK ,
NO BEFORE JOURNAL,
NO AFTER JOURNAL,
CHECKSUM = DEFAULT
(
SVC_ACCESS_ID INTEGER,
DEVICE_ID_NUMBER FLOAT,
DEVICE_END_DT DATE FORMAT ‘yyyy/mm/dd’
)
PRIMARY INDEX ( SVC_ACCESS_ID, DEVICE_ID_NUMBER )
However, I do not want the decimal places to show.
That is, instead of this: 354142020199461.00
I want to see this: 354142020199461
Can this be done? Thanks!
Oops! I just realized that the following will work:
DEVICE_ID_NUMBER DECIMAL(20,0)
Thanks anyway! If you happen to have something else that works as well, then please feel free to post it. It is always interesting to see the different solutions.
Popular Articles