How can I create rows without an underlying table in IBM DB2 SQL? 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)
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 (&lt;row1&gt;),(&lt;row2&gt;),(...)

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...