You know, I've been doing this CF/Oracle stuff for a long time, and I just figured out something I'd not figured out before. You can't use the null attribute of a cfqueryparam with a list with Oracle (at least not with our drivers). I don't know about other dbs. In sql plus it's perfectly acceptable to do:
SELECT mycolumn FROM mytable WHERE id IN (null) But, if you do this in CF: SELECT mycolumn FROM mytable WHERE id IN (<cfqueryparam cfsqltype="cf_sql_integer" value="#myvalue#" list="yes" null="yes">) You'll get an error that says: [Macromedia][Oracle JDBC Driver][Oracle]ORA-00936: missing expression. But, you can run the query in CF without the queryparam, passing in null and it works just fine. This seems like a bug to me. Can anyone verify? We're on CF 6.1 still - so maybe this is fixed in 7? I tested in Oracle 8i and 10g, with no difference. Here's a sample workaround, btw - which I find preferable to just not using cfqueryparam: WHERE a.productid <cfif listlen(client.productcart)>IN (<cfqueryparam cfsqltype="cf_sql_varchar" value="#client.productcart#" list="yes">)<cfelse> IS NULL</cfif> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Find out how CFTicket can increase your company's customer support efficiency by 100% http://www.houseoffusion.com/banners/view.cfm?bannerid=49 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:220012 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
