> -----Original Message----- > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] > Sent: Thursday, January 31, 2008 10:23 PM > To: Derby Discussion > Subject: Re: names of columns in VALUES > > Jim Newsham <[EMAIL PROTECTED]> writes: > > >> From: Army [mailto:[EMAIL PROTECTED] > [...] > >> 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
