How to reference an implicitly named column in DB2

Question

In DB2 LuW, I'm running the following query

db2 => select 'a', 'b' from sysibm.dual

1 2
- -
a b

  1 record(s) selected.

Notice that the generated "column names" are the column indexes starting with 1. Now, if I have such a query as a derived table, I cannot just simply reference the column names by their index like this:

db2 => select t."1", t."2" from (select 'a', 'b' from sysibm.dual) t
SQL0206N  "T.1" is not valid in the context where it is used.  SQLSTATE=42703

Despite the fact that numeric column aliases are possible, so this works:

db2 => select t."1", t."2" from (select 'a' as "1", 'b' as "2" from sysibm.dual) t

1 2
- -
a b

  1 record(s) selected.

Or, using derived column lists:

select t."1", t."2" from (
  select 'a', 'b' from sysibm.dual
) t("1", "2")

Is there any way to reference the generated column names / indexes without modifying the original query, nor wrapping it in a derived table (which may have side effects such as losing ordering)?

Notice the original query may not be under my control as it is provided by some other logic., e.g. in the context of jOOQ

Answer 1

You can’t reference unnamed (with a system generated name) columns in Db2.