Search code examples
sqlteradatadual-table

What is Teradata's equivalent for Oracle's DUAL

In Oracle, we can write this to generate a single row using a SELECT statement.

SELECT 1 AS x FROM dual

What is Teradata's equivalent?

Solution

  • Generally, no such table is needed

    In most cases, no table is really needed in the Teradata database. The following is valid SQL (just like in H2, PostgreSQL, Redshift, SQL Server, SQLite, Sybase ASE, Sybase SQL Anywhere, Vertica)

    SELECT 1
SELECT 1 WHERE 1 = 1

    Exceptions

    However, there is an exception, when a set operation is desireable. E.g. this is invalid in Teradata:

    SELECT 1 UNION ALL SELECT 2

    Yielding this error:

    A SELECT for a UNION,INTERSECT or MINUS must reference a table.

    But since the FROM clause is generally optional, it's very easy to emulate a DUAL table as follows:

    SELECT 1 FROM (SELECT 1 AS "DUMMY") AS "DUAL"
UNION ALL 
SELECT 2 FROM (SELECT 1 AS "DUMMY") AS "DUAL"

    Compatibility

    In case compatibility needs to be achieved with Oracle etc, it is easy to create a view that behaves like Oracle's dual:

    CREATE VIEW "DUAL" AS (SELECT 1 AS "DUMMY");

    Notice that DUAL is a keyword in Teradata, thus the view needs to be quoted.

    Other dialects

    In case anyone is interested, the jOOQ user manual lists various ways of emulating DUAL (if it's required) in 30+ SQL dialects.