db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Jim Newsham" <jnews...@referentia.com>
Subject RE: names of columns in VALUES
Date Fri, 01 Feb 2008 20:22:34 GMT


> -----Original Message-----
> From: Knut.Hatlen@Sun.COM [mailto:Knut.Hatlen@Sun.COM]
> Sent: Thursday, January 31, 2008 10:23 PM
> To: Derby Discussion
> Subject: Re: names of columns in VALUES
> 
> 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

Ah!  I hadn't used CAST before.  So the problem is that the sql compiler
needs to know the data type of the columns, and in this context it could not
be inferred, so I need to explicitly cast, right?  After casting to
timestamp, it now works as desired.  Thanks!

Jim




Mime
View raw message