db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Knut Anders Hatlen <Knut.Hat...@Sun.COM>
Subject Re: names of columns in VALUES
Date Fri, 01 Feb 2008 08:22:52 GMT
Jim Newsham <jnewsham@referentia.com> writes:

>> From: Army [mailto:qozinx@gmail.com]
[...]
>> 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

Does it work if you replace the first ? with CAST(? AS VARCHAR(128))?

-- 
Knut Anders

Mime
View raw message