On 01/06/2012 16:36, Knut Anders Hatlen wrote:
John English <checkpoint.je@gmail.com> writes:

I'm having trouble with the following error: "Conglomerate could not be
created". It happens when I do this:

  INSERT INTO resource_usage (resid,itemid,itemtype)
    (SELECT resid,?,? FROM resource_usage
     WHERE itemid=?
     AND   itemtype=?
     AND   NOT EXISTS (SELECT resid FROM resource_usage
                       WHERE itemid=? AND itemtype=?));
I think Derby doesn't allow untyped parameters in the select list. For
example, this fails:

ij> prepare ps as 'select ? from sysibm.sysdummy1';
ERROR 42X34: There is a ? parameter in the select list.  This is not allowed.

Whereas this works:

ij> prepare ps as 'select cast(? as int) from sysibm.sysdummy1';
ij> execute ps using 'values 1';
1          
-----------
1          

Thanks for the suggestion, but unfortunately that isn't the solution. I tried what you said and got the same error. I also turned on statement logging and both versions (with and without cast) compile, but they fail when executed:

Begin compiling prepared statement: INSERT INTO resource_usage (resid,itemid,itemtype) (SELECT resid,?,? FROM ...
End compiling prepared statement: INSERT INTO resource_usage (resid,itemid,itemtype) (SELECT resid,?,? FROM ...
Executing prepared statement: INSERT INTO resource_usage (resid,itemid,itemtype) (SELECT resid,?,? FROM ...

I also tried using a MessageFormat to sub the values into the query to eliminate the first two parameters, and again got the same thing.

So I'm still thrashing around in search of a solution...
--
John English If we were to define a religion to be a system of thought which contains unprovable statements, so it contains an element of faith, then Gödel has taught us that not only is mathematics a religion but it is the only religion able to prove itself to be one.
John Barrow, Pi in the Sky