The only problem I see is that if you end up with only an OR after the 1=1, the OR becomes essentially meaningless. 1=1 is always true, so all rows with the right id will be returned regardless of the OR clause.
> -----Original Message----- > From: daniel kessler [mailto:[EMAIL PROTECTED] > Sent: Friday, December 01, 2006 8:02 AM > To: CF-Talk > Subject: Re: Query WHERE separation > > That seems to have worked. I did it with a 1=1 because there might not be > anything in the brackets, but I dunno if it's needed. Here's what I ended > up with (below), please tell me if I need to make any corrections. > And thanks both of ya for the assistance. > > SELECT meeting_name,type,note_date,id,n_r_id, ROWNUM AS r, > COUNT(meeting_name) OVER() AS rowcount > FROM notes_to_the_record > WHERE ID = #session.user.id# AND > (1=1 > <cfif url.meeting_name neq ""> > AND UPPER(meeting_name) LIKE <cfqueryparam > value="%#UCase(url.meeting_name)#%" cfsqltype="cf_sql_varchar"> > </cfif> > <cfif url.type neq ""> > #url.and_or_1# UPPER(type) LIKE <cfqueryparam > value="%#UCase(url.type)#%" cfsqltype="cf_sql_varchar"> > </cfif> > <cfif url.notes neq ""> > #url.and_or_2# UPPER(notes) LIKE <cfqueryparam > value="%#UCase(url.notes)#%" cfsqltype="cf_sql_varchar"> > </cfif> > > ) > ORDER BY UPPER(meeting_name) ASC > ) > > > Use brackets to split the where clause up: > > WHERE > > id_field=id > > and ( > > a=1 > > or > > b=2 > > or > > c=3 > > ) > > > > -- > > Tom Chiverton > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:262400 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
