What is Teradata's equivalent for Oracle's DUAL

sql teradata dual-table
Lukas Eder picture Lukas Eder · Apr 18, 2018 · Viewed 13.2k times · Source

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?

Answer

Lukas Eder picture Lukas Eder · Apr 18, 2018

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.

Related questions

SQL SELECT multi-columns INTO multi-variable

I'm converting SQL from Teradata to SQL Server in Teradata, they have the format SELECT col1, col2 FROM table1 INTO @variable1, @variable2 In SQL Server, I found SET @variable1 = ( SELECT col1 FROM table1 ); That only allows a single column/variable …

Read More
What is ROWS UNBOUNDED PRECEDING used for in Teradata?

I am just starting on Teradata and I have come across an Ordered Analytical Function called "Rows unbounded preceding" in Teradata. I tried several sites to learn about the function but all of them uses a complicated example explaining the …

Read More
Find which rows have different values for a given column in Teradata SQL

I am trying to compare two addresses from the same ID to see whether they match. For example: Id Adress Code Address 1 1 123 Main 1 2 123 Main 2 1 456 Wall 2 2 456 Wall 3 1 789 Right 3 2 100 Left I'm just trying to figure out whether the address for each ID …

Read More