> -----Original Message-----
> From: Army [mailto:[EMAIL PROTECTED]
> Sent: Wednesday, January 30, 2008 9:20 AM
> To: Derby Discussion
> Subject: Re: names of columns in VALUES
>
> Jim Newsham wrote:
> >
> > It seems there is no way to name the columns produced by the VALUES
> > keyword.
>
> <snip>
>
> > Select a, b, c from x, (values ("a", "b", "c")) as y where x.a = y.1;
>
> Two things with this statement:
>
> 1. String literals should be enclosed in single quotes
> 2. You can name the columns from the subquery using the AS clause..
>
> So maybe try something like:
>
> select a, b, c
> from x, (values ('a', 'b', 'c')) as y (y1,y2,y3)
> where x.a = y.y1
This works fine for me unless the values in the VALUES clause are all
parameterized (i.e., with "?" placeholders in prepared statements). I can
work around this problem by interpolating the values directly into the
string, but I'll lose the benefit of Derby's prepared statement cache, which
will really slow down all my queries.
Here is the error message I get:
Caused by: ERROR 42Y10: A table constructor that is not in an INSERT
statement has all ? parameters in one of its columns. For each column, at
least one of the rows must have a non-parameter.
Here is an offending query:
select distinct sample.time, sample.value from int_sample as sample, (values
((?), (?))) as subquery(time) where sample.fk_band_id = ? and sample.time =
subquery.time order by sample.time asc
If I directly interpolate the first parameter into the query string as
follows, the error goes away:
select distinct sample.time, sample.value from int_sample as sample, (values
((' 1969-12-31 14:00:04.0'), (?))) as subquery(time) where sample.fk_band_id
= ? and sample.time = subquery.time order by sample.time asc
Is there a good reason why Derby does not allow all of the values to be
parameterized? Should I file a JIRA issue?
[By the way, I realize the query can be rewritten in a different way to
work, but this is not an option for me due to the way these queries are
automatically generated and spliced together.]
Thanks,
Jim
