Hi everyone,
It seems there is no way to name the columns produced by the VALUES keyword.
In ij, the output columns are always labeled 1, 2, 3, etc. Does this mean
the columns are unnamed and ij is just using numbers as labels, or are they
actually named "1", "2", "3", etc.? In any case, Derby is not letting me
use numbers as column names in my sql expression, which means that I can't
use a values clause as a correlated subquery.
As an abstract example, in the following I'd like to correlate the first
column of y ("y.1", although this syntax does not work) with the first
column of x.
Select a, b, c from x, (values ("a", "b", "c")) as y where x.a = y.1;
It would be useful to be able to rename the columns produced by the values
keyword. The following syntax is probably not adequate for the general
case, but it demonstrates the desired behavior:
Select a, b, c from x, (values ("a" as a, "b", "c")) as y where x.a = y.a;
On a slightly related note, for orthogonality it would also be useful to be
able to produce a resultset with a finite number of columns but zero
records, using the VALUES keyword. For hand-written sql, this might be
useless, but for some of the auto-generated sql I produce, it would save
some special casing and code contortion. But this one is minor. the
renaming would be much more helpful.
Regards,
Jim Newsham
