How I would deal with it would be to pass this to a variable first, and manipulate as needed.
<CFSET Request.GroupNameToSQL=replace(session.groupname,"()","(NULL)")> The SQL would read: security IN #PreserveSingleQuotes(Request.GroupNameToSQL)# >I am having problems with Oracle and this piece of SQL in Coldfusion. In >the where clause > >security IN (#PreserveSingleQuotes(session.groupname)#) > >part of the sql, if this does not have a value it falls over as Oracle >interprets this as > >security IN () OR security = 'all' ) > >It does not like the () and returns the error ORA-00936: missing expression >. > >How can I put in logic so if there is no value in (session.groupname) it >would return as a NULL ?? > >i.e. >security IN (NULL) >OR >security = 'all' > >Any ideas ?? > ><cfset session.groupname = QuotedValueList(UserGroups.groupname)> > ><CFQUERY name="list" datasource="intranetv8"> >SELECT * >FROM itlinks >WHERE linkarea='#URL.area#' >AND >( >security IN (#PreserveSingleQuotes(session.groupname)#) >OR >security = 'all' >) >ORDER BY linkid desc ></CFQUERY> > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Your ad could be here. Monies from ads go to support these lists and provide more resources for the community. http://www.fusionauthority.com/ads.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
