15.00 - Expressions - Teradata Database

Teradata Database SQL Fundamentals

prodname
Teradata Database
vrm_release
15.00
category
Programming Reference
featnum
B035-1141-015K

Expressions

An expression, which specifies a value, can consist of literals (or constants), name references, or operations using names and literals.

Scalar Expressions

A scalar expression produces a single number, character string, byte string, date, time, timestamp, or interval.

A value expression has exactly one declared type common to every possible result of evaluation. Implicit type conversion rules apply to expressions.

Query Expressions

Query expressions operate on table values and produce rows and tables of data. Query expressions can include a FROM clause, which operates on a table reference and returns a single-table value.

Zero-table SELECT statements do not require a FROM clause.

Literals

Literals, or constants, are values coded directly in the text of an SQL statement, view or macro definition text, or CHECK constraint definition text. In general, the system is able to determine the data type of a literal by its form.

Numeric Literals

A numeric literal (also referred to as a constant) is a character string of 1 to 40 characters selected from the following:

  • Digits 0 through 9
  • Plus sign
  • Minus sign
  • Decimal point
  • There are three types of numeric literals: integer, decimal, and floating point.

     

    Type

    Description

    Integer Literal

    An integer literal declares literal strings of integer numbers. Integer literals consist of an optional sign followed by a sequence of up to 10 digits.

    A numeric literal that is outside the range of values of an INTEGER is considered a decimal literal.

    Hexadecimal integer literals also represent integer values. A hexadecimal integer literal specifies a string of 0 to 62000 hexadecimal digits enclosed with apostrophes followed by the characters XI1 for a BYTEINT, XI2 for a SMALLINT, XI4 for an INTEGER, or XI8 for a BIGINT.

    Decimal Literal

    A decimal literal declares literal strings of decimal numbers.

    Decimal literals consist of the following components, reading from left-to-right: an optional sign, an optional sequence of up to 38 digits (mandatory only when no digits appear after the decimal point), an optional decimal point, an optional sequence of digits (mandatory only when no digits appear before the decimal point). The scale and precision of a decimal literal are determined by the total number of digits in the literal and the number of digits to the right of the decimal point, respectively.

    Floating Point Literal

    A floating point literal declares literal strings of floating point numbers.

    Floating point literals consist of the following components, reading from left-to-right: an optional sign, an optional sequence of digits (mandatory only when no digits appear after the decimal point) representing the whole number portion of the mantissa, an optional decimal point, an optional sequence of digits (mandatory only when no digits appear before the decimal point) representing the fractional portion of the mantissa, the literal character E, an optional sign, a sequence of digits representing the exponent.

    DateTime Literals

    Date and time literals declare date, time, or timestamp values in a SQL expression, view or macro definition text, or CONSTRAINT definition text.

    Date and time literals are introduced by keywords. For example:

       DATE '1969-12-23'

    There are three types of DateTime literals: DATE, TIME, and TIMESTAMP.

     

    Type

    Description

    DATE Literal

    A date literal declares a date value in ANSI DATE format. ANSI DATE literal is the preferred format for DATE constants. All DATE operations accept this format.

    TIME Literal

    A time literal declares a time value and an optional time zone offset.

    TIMESTAMP Literal

    A timestamp literal declares a timestamp value and an optional time zone offset.

    Interval Literals

    Interval literals provide a means for declaring spans of time.

    Interval literals are introduced and followed by keywords. For example:

       INTERVAL '200' HOUR

    There are two mutually exclusive categories of interval literals: Year-Month and Day-Time.

     

    Category

    Type

    Description

    Year-Month

  • YEAR
  • YEAR TO MONTH
  • MONTH
  • Represent a time span that can include a number of years and months.

    Day-Time

  • DAY
  • DAY TO HOUR
  • DAY TO MINUTE
  • DAY TO SECOND
  • HOUR
  • HOUR TO MINUTE
  • HOUR TO SECOND
  • MINUTE
  • MINUTE TO SECOND
  • SECOND
  • Represent a time span that can include a number of days, hours, minutes, or seconds.

    Character Literals

    A character literal declares a character value in an expression, view or macro definition text, or CHECK constraint definition text.

     

    Type

    Description

    Character literal

    Character literals consist of 0 to 31000 bytes delimited by a matching pair of apostrophes. A zero‑length character literal is represented by two consecutive apostrophes ('').

    Hexadecimal character literal

    A hexadecimal character literal specifies a string of 0 to 62000 hexadecimal digits enclosed with apostrophes followed by the characters XCF for a CHARACTER data type or XCV for a VARCHAR data type.

    Unicode character string literal

    Unicode character string literals consist of 0 to 31000 Unicode characters and are useful for inserting character strings containing characters that cannot generally be entered directly from a keyboard.

    Graphic Literals

    A graphic literal specifies multibyte characters within the graphic repertoire.

    Period Literals

    A period literal specifies a constant value of a Period data type. Period literals are introduced by the PERIOD keyword. For example:

       PERIOD '(2008-01-01, 2008-02-01)'

    The element type of a period literal (DATE, TIME, or TIMESTAMP) is derived from the format of the DateTime values specified in the string literal.

    Related Topics

     

    For more information on, for example…

    See …

    data type conversions

    SQL Functions, Operators, Expressions, and Predicates.

    query expressions

    SQL Data Manipulation Language.

    numeric, DateTime, interval, character, graphic, period, object name and hexadecimal literals

    SQL Data Types and Literals.

  • aggregate functions
  • arithmetic expressions
  • built-in functions
  • byte functions
  • calendar functions
  • CASE expressions
  • comparison operators and functions
  • DateTime functions and expressions
  • interval expressions
  • logical predicates
  • ordered analytical functions
  • set operators
  • string operators and functions
  • user-defined functions
  • SQL Functions, Operators, Expressions, and Predicates.