What is Teradata's equivalent for Oracle's DUAL

Question

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.