Rules - Advanced SQL Engine - Teradata Database

Example 1: Simple SQL UDF Definition Using a CASE Expression in its RETURN Statement

The following CREATE FUNCTION request performs an addition operation if the first argument submitted to calc is the literal ‘A’, a subtraction operation if the first argument is ‘S’, a multiplication operation if the first argument is ‘M’, and a division operation if the first argument is anything else.

     CREATE FUNCTION calc (func CHARACTER(1), A INTEGER, B INTEGER) 
     RETURNS INTEGER
     LANGUAGE SQL
     DETERMINISTIC
     CONTAINS SQL
     SQL SECURITY DEFINER
     COLLATION INVOKER
     INLINE TYPE 1
     RETURN CASE 
              WHEN func = 'A' 
              THEN A + B
              WHEN func = 'S' 
              THEN A - B
              WHEN func = 'M' 
              THEN A * B
              ELSE A / B
            END;

The following SELECT request returns a result value of 5.

     SELECT calc('A', 2, 3);

The following SELECT request returns a result value of 6.

     SELECT calc('M', 2, 3);

Example: Defining an SQL UDF With a CASE Expression in its RETURN Statement

Similar to “Example 1: Simple SQL UDF Definition Using a CASE Expression in its RETURN Statement," this CREATE FUNCTION request specifies a CASE expression in its RETURN statement.

     CREATE FUNCTION test.case_expr (a INTEGER, b INTEGER)
     RETURNS CHARACTER(50)
     LANGUAGE SQL
     DETERMINISTIC
     CONTAINS SQL
     SPECIFIC test.case_expr
     CALLED ON NULL INPUT
     SQL SECURITY DEFINER
     COLLATION INVOKER
     INLINE TYPE 1
     RETURN CASE 
              WHEN a > 1 
              THEN 'a is greater than 1'
              WHEN b > 1 
              THEN 'b is greater than 1'
              ELSE 'a and b are not greater than 1'
            END;