17.00 - Rules - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL Data Definition Language Syntax and Examples

Product
Advanced SQL Engine
Teradata Database
Release Number
17.00
Release Date
September 2020
Content Type
Programming Reference
Publication ID
B035-1144-170K
Language
English (United States)

The following rules apply to the RETURN statement.

  • RETURN is an SQL control statement that can only be executed from within an SQL UDF.
  • You cannot specify more than one RETURN statement per SQL UDF.
  • The data type of value_expression must match the data type specified by the RETURNS clause of the SQL UDF definition or it must be capable of being cast implicitly to the data type specified in the RETURNS clause.

    The specified data types can be any valid Teradata type.

  • value_expression cannot contain references to tables.
  • value_expression cannot contain scalar subqueries such as those made by DML statements.
  • value_expression can contain references to parameters, constants, SQL UDFs, external UDFs, and methods.
  • value_expression cannot be a conditional expression or have a Boolean return type.
  • value_expression can contain arithmetic values and functions, string functions, DateTime functions, and SQL operators that define a scalar result.
  • value_expression cannot contain aggregate or ordered analytic functions.
  • If you specify a reference to an SQL UDF in the RETURN statement, it cannot be any of the following types of reference:
    • Self-references
    • Circular references
    • Forward references

      A forward reference is a case where an SQL UDF references another SQL UDF that does not yet exist.

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;