ibatis/Oracle - SELECT query inside INSERT is failing

Question

Hi,

I'm trying to do an Insert operation using iBatis.

    INSERT INTO SCHEMA.TABLE
       (FIELD1, 
        FIELD2, 
        FIELD3)
     VALUES
       (#field1#, 
            (SELECT 
                ANOTHER_FIELD
            FROM 
                SCHEMA.TABLE
            WHERE 
                FIELD4= #field2#), 
        #field2#)

The inner select query always fails and returns NULL. But if I substitute #field2# with the actual value only in the inner query, it works fine. Why is iBatis not substituting fieldvalues in the innerqueries?

Any ideas?

Answer 1

The following way using a single sub-query and omitting the VALUES keyword would work with Oracle, please try with iBatis:

INSERT INTO SCHEMA.TABLE
   (FIELD1, 
    FIELD2, 
    FIELD3)
   (
        SELECT
            #field1#, 
            ANOTHER_FIELD,
            #field2#
        FROM 
            SCHEMA.TABLE
        WHERE 
            FIELD4= #field2#
   )

Answer 2

That syntax is invalid for Oracle. Try the following:

INSERT INTO SCHEMA.TABLE
   (FIELD1, 
    FIELD2, 
    FIELD3) 
   SELECT 
       #field1#,
       ANOTHER_FIELD,
       #field2#
   FROM 
       SCHEMA.TABLE
   WHERE 
       FIELD4= #field2#