CF 9 Upgrade Issue

[rajva]

We recently upgraded from MX 7 to ColdFusion 9 on glassfish 2.1.1. I'm getting the following error:

Error Executing Database Query.ORA-00933: SQL command not properly ended

when executing the following query:

<cfquery name="InsertSubElem3" datasource="#Session.Datasource#">

                INSERT INTO AFS_MASTER_UTILITIES

                (PROGRAM_CD, CMPCODE, ELMCODE, ELMLEVEL, UPDATE_DATE, SWITCH, VALUE)

                (SELECT 'AFS_SUBANALYSIS_SUSP', CMPCODE, CODE, ELMLEVEL, SYSDATE, SUBANAL, 'SUBANALYSIS REMOVE'

                FROM OAS_ELEMENT

                WHERE ELMLEVEL = 3 AND

                DELDATE IS NULL AND

                                ((SUBANAL  = 76) or

                                (SUBANAL = 46 and CODE in (SELECT CODE FROM OAS_COMPANY))))

</cfquery>

This is working fine in the MX 7. If I run the query directly from the database it is working fine. After my troubleshooting what I observed that if I remove the select and insert the hard coded values, it is working. Basically it is not liking the select in the insert statement.

We are using ojdbs6.jar, We tried with ojdbc14.jar also but without any success. database is in Oracle 10g.

Any help is appreciated.

Thanks,

Raj

[Dan_Bracuk]

It's possible that that number of open parentheses is not equal to the number of close parentheses.  I didn't count, but you sure have a lot at the end.

Also, you don't need the parentheses around the select portion of your query.

[rajva]

As I mentioned earlier, the syntax is correct, the same syntax is working in the existing CF 7 and also when I run manually in the database. I tried by removing the parentheses as you suggested and it's the same error. So it is not the issue with syntax, it is issue with CF 9 / glass fish environment.