How can I create rows without an underlying table in IBM DB2 SQL?

Question

In T-SQL, I can execute the following:

SELECT      *
FROM        (SELECT 1 AS n UNION ALL SELECT 2 AS n) AS t;

In IBM DB2 SQL, this fails with:

[IBM][System i Access ODBC Driver][DB2 for i5/OS]SQL0199 - Keyword UNION not expected. Valid tokens: , FROM INTO.

A SELECT statement seems to require a FROM clause. Indeed, a reductive SQL statement such as:

SELECT 1 AS n

Also errors with:

[IBM][System i Access ODBC Driver][DB2 for i5/OS]SQL0104 - Token was not valid. Valid tokens: , FROM INTO.

There is an UNNEST function which can create rows from an array, but it seems the array must already be represented by something like a variable or parameter.

I understand I could work around this by doing a SELECT from an arbitrary table with an impossible criterion such as 1 = 0, but that seems to be a silly requirement.

So how can I construct rows without an underlying table?

(keywords: tally table; derived table)

Answer 1

DB2 for i5 V5R4 does not support the VALUES statement as a table reference, so examples provided in another answer won't work in that version. However, all versions of DB2 offer a single row pseudo-table SYSIBM.SYSDUMMY1, which can be used to generate rows:

SELECT 1 AS n FROM SYSIBM.SYSDUMMY1
UNION ALL 
SELECT 2 AS n FROM SYSIBM.SYSDUMMY1

Answer 2

Platform and version are important when asking about Db2...

In this case it appears you're running Db2 for IBM i (iSeries, AS/400)

What you are looking for is known in the SQL standards as table value constructor which looks like VALUES (<row1>),(<row2>),(...)

On Db2 for i (and LUW I believe) the following are a couple of ways...

-- Using a Common Table Expression (CTE)
WITH X(foo, bar, baz) AS (
VALUES (0, 1, 2), (3, 4, 5), (6, 7, 8)
) SELECT * FROM X;


-- Using a Nested Table Expression (NTE)
SELECT * FROM TABLE (
VALUES (0, 1, 2), (3, 4, 5), (6, 7, 8)
) X(foo, bar, baz);

However, you appear to be using an older version of Db2 for i (v5?) I don't recall if that version supports table value constructors...