You can't use a bind parameter, or any parameter, after an IS clause. It's reserved solely for NULL comparisons. If you turn off ANSI_NULLS then you could do this:
AND FOO = <cfqueryparam value="#arguments.FOO#" cfsqltype="cf_sql_integer" null="#YesnoFormat(arguments.FOO EQ 0)#"/> but that may affect other objects in the DB. -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Behalf Of Adrian J. Moreno Sent: Thursday, July 21, 2005 11:31 AM To: [email protected] Subject: Oracle, <cfqueryparam> and NULLs I'm converting an application from MySQL to Oracle. 95% of the SQL syntax works for either db, I'm just trying to fix the 5% that doesn't. Specs: CFMX 7 J2EE & Oracle 10g Linux In a thread on CF-Talk, someone pointed out that you could use YesNoFormat() to set the null attribute of <cfqueryparam>. I've rarely ever used YesNoFormat(), so it was like a "well, duh" moment for me. tableOne.FOO is an integer value, NOT NULL and FOO is PK of tableOne. tableTwo.FOO is an integer value, NULL and references tableOne.FOO as FK constraint. So code that had been <cfif arguments.foo EQ 0> AND FOO IS NULL <cfelse> AND FOO = <cfqueryparam value="#arguments.FOO#" cfsqltype="cf_sql_integer" /> </cfif> could be slimmed down to one line like this: AND FOO <cfif arguments.FOO EQ 0>IS<cfelse>=</cfif> <cfqueryparam value="#arguments.FOO#" cfsqltype="cf_sql_integer" null="#YesnoFormat(arguments.FOO EQ 0)#"/> This works on MySQL and SQL Server, but oracle's having a fit. It tells me "missing NULL keyword". <cfdump>ing the <cfcatch> contents shows AND FOO IS (param 1) . . . (param 1) = [type='IN', value='null', sqltype='cf_sql_integer'] so it's sending null in correctly. It even throws an error using AND FOO IS <cfqueryparam value="" cfsqltype="cf_sql_integer" null="yes"/> It only works if I go back to the original, verbose code and specifically write "AND FOO IS null". Anyone have an idea why Oracle can't handle this? Thanks, Adrian ---------------------------------------------------------- To post, send email to [email protected] To unsubscribe: http://www.dfwcfug.org/form_MemberUnsubscribe.cfm To subscribe: http://www.dfwcfug.org/form_MemberRegistration.cfm ---------------------------------------------------------- To post, send email to [email protected] To unsubscribe: http://www.dfwcfug.org/form_MemberUnsubscribe.cfm To subscribe: http://www.dfwcfug.org/form_MemberRegistration.cfm
