ibatis/Oracle - SELECT query inside INSERT is failing

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?

From stackoverflow

• 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#
     )
  

  whoopy_whale : It works! Thanks a lot...

  Jeffrey Kemp : Note: the parentheses around the SELECT are optional.

• 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#