I have this sample database:
CREATE TABLE t_arrays (
id integer not null,
string_array ARRAY,
number_array ARRAY,
date_array ARRAY,
CONSTRAINT pk_t_arrays PRIMARY KEY (ID)
);
INSERT INTO t_arrays VALUES (1, null, null, null);
INSERT INTO t_arrays VALUES (2, (), (), ());
INSERT INTO t_arrays VALUES (3, ('a'), (1), ('1981-07-10'));
INSERT INTO t_arrays VALUES (4, ('a', 'b'), (1, 2), ('1981-07-10',
'2000-01-01'));
Now I would like to use the TABLE function as documented on this section of
the manual:
http://www.h2database.com/html/functions.html#table
The goal is to self-join the t_arrays table in order to unnest the
string_array for instance. The desired output would then be:
+----+--------------+
| ID | COLUMN_VALUE |
+----+--------------+
| 2 | a |
| 3 | a |
| 3 | b |
+----+--------------+
Intuitively, I'd expect this to work, but it fails. The self-join on id =
t1.id is not recognised:
select t1.id, t2.column_value
from t_arrays t1
join table(column_value varchar = (select string_array from t_arrays where
id = t1.id)) t2
This works, so my understanding of syntax seems to be correct:
select t1.id, t2.column_value
from t_arrays t1
join table(column_value varchar = (select string_array from t_arrays where
id = 4)) t2
Is there a way to do this with H2? I think being able to reference tables /
columns from the outer scope of the TABLE function is important, not only
when joining new nested selects, but also when unnesting arrays from stored
functions, e.g. something like this:
select t1.id, t2.column_value
from t_arrays t1
join table(column_value varchar = my_array_function(t1.id)) t2
--
You received this message because you are subscribed to the Google Groups "H2
Database" group.
To view this discussion on the web visit
https://groups.google.com/d/msg/h2-database/-/fv9Vdl9u0z0J.
To post to this group, send email to [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/h2-database?hl=en.
