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

*”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)?”* to tell you a little trade secret without ORDER BY there isnt anny order you can trust on as SQL resultsets are by SQL standards orderless in fact the resultset will be non derministic (random) the only valid way into getting true derministic (fixed) results is including a column in the order by that has a primary or unique key or using a column where you know has unique values — Raymond Nijland, May 07 '19 at 12:31
@RaymondNijland: Thanks Raymond, well understood. That is totally not the question here, though :) — Lukas Eder, May 07 '19 at 12:39
I know still i wanted to warn you, What if you would generate a array ARRAY[“a”, “b”] and use the array indexes which are numeric to select the item you need. — Raymond Nijland, May 07 '19 at 12:45
I'd guess that adding the 1, 2 etc as a column label only happens when the result set is produced, and not in the intermediate results. — Mark Rotteveel, May 07 '19 at 12:51
Have you checked how these colums appear by other tools than "db2" cli? If they are unnamed at all, the 1, 2, ... could come from the CLI? — Markus Winand, May 07 '19 at 12:51
In what context do you want to reference them? In JDBC, for example, you can retrieve unnamed columns by their index: `rs.getString(2)`. Can you elaborate? — The Impaler, May 07 '19 at 12:53
@RaymondNijland: Feel free to post an answer with a workaround. Do note though, that I'm really curious about this *specific* problem as described in my question, so I'm curious about how to reference these generated column names (or the columns per se). — Lukas Eder, May 07 '19 at 12:57
@MarkRotteveel: Well the column names are still there when I write `select * from (select 'a', 'b' from sysibm.dual) t;` — Lukas Eder, May 07 '19 at 12:58
@TheImpaler: I'm maintaining [jOOQ](https://www.jooq.org). I want to reference them in *any* context :) (especially when transforming the SQL statement to something else). — Lukas Eder, May 07 '19 at 12:59
"The column names are still there", not necessarily. I guess there are no column names in the derived table (nor actually in the top-level select), and that the existence of those names may just be an artifact of the metadata produced for the result set (as in "oh, an unnamed column, we use its position as a name"). — Mark Rotteveel, May 07 '19 at 13:03
@MarkRotteveel: Of course you're probably right. So how to reference those columns? :-) — Lukas Eder, May 07 '19 at 13:08
@LukasEder For what is worth, on our side we are also generating live SQL in Java in our ORM, and we are facing multiple issues like this one when producing subqueries. — The Impaler, May 07 '19 at 13:46
Is this what you mean by derived table: `with query(a,b) as (select 'a', 'b' from sysibm.sysdummy1) select query.A, query.B from query;`? — Voooza, May 07 '19 at 16:29
No, I mean the *derived table*, as in the standard SQL concept of a [*derived table*](https://en.wikipedia.org/wiki/SQL_syntax#Derived_table) — Lukas Eder, May 07 '19 at 18:22
@LukasEder but I guess that "with" clause solution would have same drawbacks as derived table right? Or does it solve your problem? — Voooza, May 07 '19 at 19:25

score 1 · Answer 1 · answered May 07 '19 at 18:49

1

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

answered May 07 '19 at 18:49

Mark Barinstein

11,456
2
8
16

score 0 · Answer 2 · answered May 07 '19 at 13:57

0

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.

answered May 07 '19 at 13:57

The Impaler

45,731
9
39
76

Thanks. That doesn't really answer my question, though :) In the case of jOOQ, such an abstraction in the API wouldn't make sense, and it was hardly ever needed, just like in SQL, as most queries can be transformed to a fitting alternative. – Lukas Eder May 07 '19 at 14:29
@LukasEder Yeah, this is a workaround. We couldn't find any way of doing it properly in SQL only. – The Impaler May 07 '19 at 14:33
Again, in most cases, it's "easy" to emulate a certain behaviour, including the generation of synthetic column names for a specific purpose. Perhaps, you should build your ORM on top of jOOQ ;-) – Lukas Eder May 07 '19 at 15:51
@LukasEder I was thinking my answer is so specific to address a code generator issue. Was wondering how many people would actually understand the crux of the issue. Just wondering on a Friday afternoon. – The Impaler May 10 '19 at 18:49

score 0 · Answer 3 · edited Feb 22 '21 at 11:45

0

"...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
*/

edited Feb 22 '21 at 11:45

yusuf hayırsever

691
7
19

answered Feb 22 '21 at 11:04

Dejan

1

Sure, we could transform the SQL to something quite different, but that may not be what a user expects. – Lukas Eder Feb 22 '21 at 14:48
Your question was: "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)?" Using a CTE does not modify the the original query. There is no side effect such as loosing ordering. The original query does not have to be transformed. What is the user's expectation? – Dejan Mar 08 '21 at 06:56
The "user" in my case is a [jOOQ](https://www.jooq.org) user who wrote this query in jOOQ, a query that has a projection without any actual column names. Renaming the columns to something specific may have a lot of subtle edge case side effects, which is why I'd like to prevent it... – Lukas Eder Mar 08 '21 at 09:01

How to reference an implicitly named column in DB2

3 Answers3