CODEWITHSUNDEEP
sqldb2db2-luw
Lukas Eder
Lukas Eder

Reputation: 220797

How to reference an implicitly named column in DB2

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

Upvotes: 3

Views: 1379

Answers (3)

Dejan
Dejan

Reputation: 1

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

How about using a common table expression?

select 'a', 'b' from sysibm.dual;
/*
1 2
- -
a b
*/

WITH cte(FIRST_COL, SECOND_COL) AS (SELECT 'a', 'b' FROM sysibm.dual)
SELECT FIRST_COL, SECOND_COL FROM   cte;
/*
FIRST_COL SECOND_COL
--------- ----------
a         b
*/

Upvotes: 0

Mark Barinstein
Mark Barinstein

Reputation: 12314

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

Upvotes: 1

The Impaler
The Impaler

Reputation: 48770

I think JOOQ is great. We have another ORM that produces live SQL and we have faced the same problem. What we end up doing was to separate the Java classes into two quite distinct SELECTs:

  • General SELECT. Accepts "columns" and "named columns".
  • Fully Named SELECT. Accepts only "named columns".

Essentially our API enforces the use of the second one on some subqueries (CTEs for example), so users are forced to either use table/view columns (named by definition), or forcefully add aliases to any free expressions they produce.

This way, any external query can always retrieve subquery columns by their name.

I tried to do this in SQL but I couldn't find any standard SQL way to retrieve unnamed subquery columns.

Upvotes: 0

Related Questions