Teradata Primer for SQL Developers: Numeric Functions

Introduction

I recently started working with Teradata for a client, and my initial line of learning has been to compare and constrast Teradata functionality with Oracle, the database I know best. This post covers Teradata numeric functions and their Oracle equivalents.  I’ve included their SQL Server and MySQL equivalents as well.

Teradata Numeric Functions

Here’s a list of Teradata numeric functions, though note that two of these (exponentiation and modulus) are actually operators.

Exponentiation Operator

Teradata uses the ** operator for exponentiation, just like many non-database programming languages. This is not an ANSI standard operator.

Oracle, SQL Server, and MySQL use the POWER function for exponentiation. MySQL also supports the shorter-named POW function.

Database Expression Result
Teradata 2 ** 5 32
Oracle POWER(2, 5) 32
SQL Server POWER(2, 5) 32
MySQL POWER(2, 5) 32
POW(2, 5) 32

Modulus Operator

Teradata uses the MOD operator for the modulus, which (for positive numbers at least) is the remainder when a number is divided by another number. This is not an ANSI standard operator. Here’s how to get the modulus in Teradata, Oracle, SQL Server, and MySQL (the examples use 5 modulus 3, which asks for the remainder when 5 is divided by 3; the answer is 2):

Database Expression Result
Teradata 5 MOD 3 2
Oracle MOD(5, 3) 2
SQL Server 5 % 3 2
MySQL MOD(5, 3) 2
5 % 3 2

ABS Function

The ABS function returns a number’s absolute value. For zero and positive numbers, this is the number itself. For negative numbers it’s the number multiplied by -1, which turns it positive. This is not an ANSI-standard function.

Oracle, SQL Server, and MySQL all support this function, and their syntax is identical to Teradata’s:

ABS(-32) → 32
ABS(79) → 79
ABS(0) → 0

CASE_N Function

The CASE_N function takes a list of logical expressions and returns the index of the first expression that evaluates to true: if the first expression is true it returns 1, if the second expression is true it returns 2, if the third expression is true it returns 3, and so forth. It includes keywords to return a value if all of the expressions are false (NO CASE), or if all of the expressions are indeterminate due to null values (UNKNOWN), or both (NO CASE OR UNKNOWN).

The CASE_N function is targeted to partitioning, where rows in a table can be stored in different data files, often on different disks. With partitioning, the table definition assigns a partition based on table values. Partitions are numbered 1 through n, and the CASE_N function is a convenient shorthand for assigning a value from 1 through n.

This function is not case sensitive, and there is no equivalent Oracle, SQL Server, or MySQL function. To get similar functionality in other databases, use the CASE construct.

The Teradata documentation for this function is good but it lacks examples, something I find useful when figuring out a function. The examples here use this table:

SELECT * FROM test_casen;

col1  col2   col3        case_example
----  -----  ----------  ------------
null  null   null        null
22    check  2010-12-23  null
123   text   2013-08-30  2

Here’s a CASE_N query using its values:

SELECT
  col1,
  col2,
  col3,
  CASE_N(
    col1 = 122,
    col2 = 'text',
    col3 = date '2013-08-30',
    NO CASE) AS case_ex
FROM test_casen
ORDER BY col1;

col1 col2  col3       case_ex
---- ----- ---------- -------
null null  null       null
22   check 2010-12-23 4
123  text  2013-08-30 2
  • For the first row, the conditions are indeterminate because of the null values so CASE_N can’t be evaluated. It returns null.
  • For the second row, the first three conditions evaluate to false. The fourth condition is NO CASE, which means “all the others are false” – think of it as like an else in an if/then/else statement. Because the fourth argument evaluates to true, the function returns 4.
  • For the third row, the second condition (col2 = 'text') evaluates to true, so CASE_N returns 2.

In this query, the CASE_N uses the UNKNOWN keyword as the fifth argument. The UNKNOWN matches the first row, where none of the other conditions evaluate to true because of the nulls:

SELECT
  col1,
  col2,
  col3,
  CASE_N(
    col1 = 122,
    col2 = 'text',
    col3 = date '2013-08-30',
    NO CASE,
    UNKNOWN) AS case_example
FROM test_casen
ORDER BY col1;

col1  col2   col3        case_example
----  -----  ----------  ------------
null  null   null                   5
  22  check  2010-12-23             4
 123  text   2013-08-30             2

Now the first row returns 5 because UNKNOWN is the fifth condition in the CASE_N.

To have CASE_N return 4 when conditions 1, 2, and 3 aren’t true or are indeterminate due to the null values, use the NO CASE OR UNKNOWN keyword for the fourth argument:

SELECT
  col1,
  col2,
  col3,
  CASE_N(
    col1 = 122,
    col2 = 'text',
    col3 = date '2013-08-30',
    NO CASE OR UNKNOWN) AS case_example
FROM test_casen
ORDER BY col1;

col1  col2   col3        case_example
----  -----  ----------  ------------
null  null   null                   4
  22  check  2010-12-23             4
 123  text   2013-08-30             2

EXP Function

The EXP function raises the natural logarithm base e to an exponent value. Its parameter is the exponent, so for example EXP(3) is e3. This is not an ANSI function.

Oracle, SQL Server, and MySQL also support the EXP function. If you ever need the value of e, call EXP(1).

LN Function

The LN function returns the natural log of a number. The natural log uses base e. This is not an ANSI standard function. Oracle and MySQL support the LN function (MySQL also supports the synonym LOG. In SQL Server, the function name is LOG.

The value of e (approximately 2.718281828459045) is used in the examples below:

Database Expression Result
Teradata LN(2.718281828459045) 1.0
Oracle LN(2.718281828459045) 1
SQL Server LOG(2.718281828459045) 1
MySQL LN(2.718281828459045) 1
LOG(2.718281828459045) 1

LOG Function

The LOG function returns the base 10 log of a number. This is not an ANSI standard function. Oracle supports the LOG function, but it requires the base (10) as the first argument. In MySQL the function is named LOG10. MySQL also has an overload for the LOG function which acts like Oracle’s LOG function. SQL Server doesn’t have a function for base-10 logarithms, but they can be calculated using the change of base identity: log10A = logbaseX(A) / logbaseX(10).

The examples below use log101000, the value of which is 3 (103 = 1,000). The change-of-base identity is also illustrated by calculating log101000 using base e (note that the change-of-base calculation has rounding issues in MySQL; the LOG10 function is the only one that returns exactly 3):

Database Expression Result
Teradata LOG(1000) 3.0
LN(1000) / LN(10) 3.0
Oracle LOG(10, 1000) 3
LN(1000) / LN(10) 3
SQL Server LOG(1000) / LOG(10) 3
MySQL LOG10(1000) 3
LOG(10, 1000) 2.9999999999999996
LOG(1000) / LOG(10) 2.9999999999999996

NULLIFZERO Function

The NULLIFZERO function returns null if the argument passed to it is zero. If the argument is any other value, it’s returned unchanged. It’s not ANSI compliant. This function useful for avoiding “divide by zero” errors. As an example, take the cost per widget query below:

SELECT cost / widgets FROM products;

If the widgets value is zero for any row, Teradata will throw a “division by zero” error. Division by zero is undefined and so, in a way, is null, so the NULLIFZERO function can be used to return a null value for rows with zero widgets:

SELECT cost / NULLIFZERO(widgets) FROM products;

For rows with zero widgets, the calculation becomes cost / NULL, and any time null is in a calculation the result is null.

Database Expression Result
Teradata NULLIFZERO(0) null
NULLIFZERO(5.2) 5.2
Teradata
Oracle
SQL Server
MySQL
NULLIF(0, 0) null
NULLIF(5.2, 0) 5.2

LOG Function

The RANDOM function returns a random integer between a specified lower and upper bound. This is not an ANSI standard function.

Oracle doesn’t have a built-in random-number generator, but random numbers can be generated using the DBMS_RANDOM package. SQL Server has a RAND function that will return a random number between zero and one, exclusive. MySQL also has a RAND function but its return value is between zero inclusive and one exclusive – in other words, the SQL Server RAND function will never return a zero but the MySQL RAND function may return a zero.

The examples below return a random integer between 1 and 10:

Database Expression
Teradata RANDOM(1, 10)
Oracle TRUNC(DBMS_RANDOM.VALUE * 10) + 1
SQL Server ROUND(RAND() * 10, 0, 1) + 1
MySQL TRUNCATE(RAND() * 10) + 1 3

Note that the SQL Server ROUND function will truncate if its optional third parameter is a non-zero value.

RANGE_N Function

This function assigns a ranking to a range of values. It’s not ANSI compliant.

RANGE_N is best explained with a few examples:

  • RANGE_N(expr BETWEEN 0, 20, 40 and 60):
    • Returns 1 if 0 <= expr < 20
    • Returns 2 if 20 <= expr < 40
    • Returns 3 if 40 <= expr < 60
    • Returns null if expr is not between 0 and 60
    • Returns null if expr is null
  • RANGE_N(expr BETWEEN *, 0, 20, 40, 60 and *):
    • Returns 1 if expr < 0
    • Returns 2 if 0 <= expr < 20
    • Returns 3 if 20 <= expr < 40
    • Returns 4 if 40 <= expr < 60
    • Returns 5 if expr > 60
    • Returns null if expr is null
  • RANGE_N(expr BETWEEN 0, 20, 40 and 60, NO RANGE, UNKNOWN):
    • Returns 1 if 0 <= expr < 20
    • Returns 2 if 20 <= expr < 40
    • Returns 3 if 40 <= expr < 60
    • Returns 4 if expr is not between 0 and 60 (NO RANGE)
    • Returns 5 if expr is null (UNKNOWN)
  • RANGE_N(expr BETWEEN 0, 20, 40 and 60, NO RANGE OR UNKNOWN):
    • Returns 1 if 0 <= expr < 20
    • Returns 2 if 20 <= expr < 40
    • Returns 3 if 40 <= expr < 60
    • Returns 4 if expr is not between 0 and 60 or if expr is null (NO RANGE OR UNKNOWN)

Note the similarities between the RANGE_N and CASE_N functions.

This function is not supported by Oracle, SQL Server, or MySQL, but the same results can be gotten with a searched CASE function. The matched CASE equivalent to RANGE_N(expr BETWEEN 0, 20, 40 and 60, NO RANGE, UNKNOWN) is:

CASE
  WHEN expr >= 0 AND expr < 20 THEN 1
  WHEN expr >= 20 AND expr < 40 THEN 2
  WHEN expr >= 40 AND expr < 60 THEN 3
  WHEN expr < 0 OR expr >= 60 THEN 4
  ELSE 5
  END

The WHEN expressions cover all possible ranges, so the fallthrough of ELSE 5 will only catch a null. Another way to do this is:

CASE
  WHEN expr >= 0 AND expr < 20 THEN 1
  WHEN expr >= 20 AND expr < 40 THEN 2
  WHEN expr >= 40 AND expr < 60 THEN 3
  WHEN expr IS NULL THEN 5
  ELSE 4
  END

These CASE statements will also work in Teradata, leaving you to choose between CASE (ANSI compliant and familiar to non-Teradata developers) or RANGE_N (more compact).

SQRT

The SQRT function returns the square root of a number. It's not ANSI compliant.

The function is identical in Oracle, SQL Server, and MySQL. Note that the argument to the function can't be negative, as the square root of a negative value isn't a real number.

  • SQRT(169) → 13
  • SQRT(7) → 1.73205081
  • SQRT(-1) → causes an error
  • WIDTH_BUCKET Function

    This ANSI SQL-2003 compliant function returns the n-tile partition for an expression. This example assigns quintiles for values from 0 to 100. If a value is less than zero its "bucket" will be 0, and if it's greater than 100 its bucket will be 6:

    SELECT WIDTH_BUCKET(<em>expr</em>, 0, 100, 5) AS Quintile</code>
    FROM ...

    Oracle supports this function; SQL Server and MySQL do not. For SQL Server and MySQL, use a searched CASE statement:

    SELECT CASE
      WHEN col1 <    0 THEN 0
      WHEN col1 <=  20 THEN 1
      WHEN col1 <=  40 THEN 2
      WHEN col1 <=  60 THEN 3
      WHEN col1 <=  80 THEN 4
      WHEN col1 <= 100 THEN 5
      ELSE 6
      END AS Quintile
    FROM ...

    The searched CASE will also work in Teradata and Oracle.

    Also note that Teradata, Oracle, and SQL Server support the analytic NTILE function, which returns similar results.

    ZEROIFNULL Function

    The ZEROIFNULL function takes one argument and returns zero if the argument is null or the argument's value if it's not null. It's not ANSI compliant.

    Examples:

    • ZEROIFNULL(99) → 99
    • ZEROIFNULL(null) → 0
    • The ZEROIFNULL function isn't supported by Oracle, SQL Server, or MySQL. For these databases, use a searched CASE instead (this also works in Teradata):

      CASE
        WHEN val IS NULL THEN 0
        ELSE val
        END;
      

Leave a Reply

Your email address will not be published. Required fields are marked *