daniel kessler wrote: > Now, since it's a search tool, I had to add another set of ANDs for all the > search parameters. I came up with something, after much ta-doo and I think > it's right, but I was hoping that y'all could check it over - mainly how I > organized the search variables with the query that was provided. I also > added parens around the WHERE content that was provided. This seems to work > with initial testing. > > SELECT n.meeting_name,n.type,n.note_date,n.notes,n.type_describe > FROM notes_to_the_record n > WHERE > (1=1 > <cfif url.meeting_name neq ""> > AND UPPER(n.meeting_name) LIKE <cfqueryparam > value="%#UCase(url.meeting_name)#%" cfsqltype="cf_sql_varchar"> > </cfif> > <cfif url.type neq ""> > #url.and_or_1# UPPER(n.type) LIKE <cfqueryparam > value="%#UCase(url.type)#%" cfsqltype="cf_sql_varchar"> > </cfif> > <cfif url.notes neq ""> > #url.and_or_2# UPPER(n.notes) LIKE <cfqueryparam > value="%#UCase(url.notes)#%" cfsqltype="cf_sql_varchar"> > </cfif> > )
The separation of your search parameters from the user filtering is fine, but within the search, you are going to run into problems... 1. If url.meeting_name neq "" AND url.and_or_1 IS "OR", then you are going to return all records, no matter what you have in url.type. 2. If you have values in all three parameters, and you have different values in the and_or fields, what are you really wanting to return is not clear... Does 1=1 AND UPPER(n.meeting_name) LIKE '%somevalue%' AND UPPER(n.type) LIKE '%anothervalue%' OR UPPER(n.notes) LIKE '%thirdvalue%' Actually mean... (1=1 AND UPPER(n.meeting_name) LIKE '%somevalue%' AND UPPER(n.type) LIKE '%anothervalue%') OR UPPER(n.notes) LIKE '%thirdvalue%' or does it mean... 1=1 AND UPPER(n.meeting_name) LIKE '%somevalue%' AND (UPPER(n.type) LIKE '%anothervalue%' OR UPPER(n.notes) LIKE '%thirdvalue%') I'm not sure about Oracle's precedence (I believe that is what you are using), but I'm betting that it will return like the one with all the ANDs grouped. I think it is a better to give the user the option of "Return records that match any parameter" and "Only return records that match ALL parameters", and then just use AND and OR consistently based on that. ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Upgrade to Adobe ColdFusion MX7 Experience Flex 2 & MX7 integration & create powerful cross-platform RIAs http:http://ad.doubleclick.net/clk;56760587;14748456;a?http://www.adobe.com/products/coldfusion/flex2/?sdid=LVNU Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:267823 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
