Solved

having a little trouble with a find statement

Posted on 2014-01-16
4
187 Views
Last Modified: 2014-01-17
I'm converting some old code;

<cfif #find("Any US",USREGION)#>
	<cfset querystring = "#querystring#" & "(USREGION IS NOT NULL)" >
	<cfset USREGIONquery = "true">
<cfelse>
	<cfif #find("Carolinas",USREGION)#>
		<cfif USREGIONquery EQ "true">
			<!--- or (USREGION Like '%Carolinas%') --->
			<cfset querystring = "#querystring#" & " or (USREGION Like '%Carolinas%')" > 
		<cfelse>
			<!--- (USREGION Like '%Carolinas%') --->
			<cfset querystring = "#querystring#" & "(USREGION Like '%Carolinas%')" >
			<cfset USREGIONquery = "true">
		</cfif>
	</cfif>
	<cfif #find("Mountain West",USREGION)#>
		<cfif USREGIONquery EQ "true">
			<!--- or (USREGION Like '%Mountain West%') --->
			<cfset querystring = "#querystring#" & " or (USREGION Like '%Mountain West%')" > 
		<cfelse>
			<!--- (USREGION Like '%Mountain West%') --->
			<cfset querystring = "#querystring#" & "(USREGION Like '%Mountain West%')" >
			<cfset USREGIONquery = "true">
		</cfif>
	</cfif>    etc......

Open in new window



To a more dynamic table based code;

<cfinvoke component="services.jobboard" method="getRegions" returnvariable="regions" />
    <cfset USREGIONquery = "false">
    <cfset USREGION = "South">
    <cfset querystring = "(" >

    <cfoutput>
        <cfif #find("Any US",USREGION)#>
            <cfset querystring = "#querystring#" & "(USREGION IS NOT NULL)" >
            <cfset USREGIONquery = "true">
        <cfelse>
            <cfloop query="regions">
                #usregion#-#regions.description#<br>
                #querystring#<br>
                <cfif #find(regions.description,USREGION)#>
                    <cfif USREGIONquery EQ "true">
                        <cfset querystring = "#querystring#" & " or (USREGION Like '%#regions.description#%')" >
                #querystring#<br>
                    <cfelse>
                        <cfset querystring = "#querystring#" & "(USREGION Like '%#regions.description#%')" >
                #querystring#<br>
                        <cfset USREGIONquery = "true">
                    </cfif>
                </cfif>
            </cfloop>
            <cfif USREGIONquery EQ "true">
                <cfset querystring = "#querystring#" & " or (USREGION Like '%All%')" >
                #querystring#<br>
            </cfif>
            <cfif querystring eq "(">
                <cfset querystring = "" >
            <cfelse>
                <cfset querystring = "#querystring#" & ")" >
            </cfif>
            #querystring# 
        </cfif>
    </cfoutput>

Open in new window


The problem is the find function doesn't seem to be working.  I've tried many variations.  The output I get is;

South-New England
(South-Northeast
(South-Mid Atlantic
(South-Southeast
(South-South
(South-Great Lakes
(South-Midwest
(South-South Central
(South-Mountain States
(South-Southern California
(South-Northern California
(South-Southwest
(South-Northwest
(
0
Comment
Question by:lanterv
  • 2
  • 2
4 Comments
 
LVL 12

Accepted Solution

by:
mkishline earned 500 total points
ID: 39788863
Couple of thoughts. The first would be to switch your "Find" to "FindNoCase" which will make the search case-insensitive. You may also want to consider doing the same thing for the conditions in your query.

For example:
<cfset querystring = "#querystring# OR (USREGION LIKE '%#regions.description#%') />

Open in new window


Would become:
<cfset querystring = "#querystring# OR (LOWER(USREGION) LIKE '%#LCase(regions.description)#%') />

Open in new window


It may have been lost during your post, but it appears that you're also missing an operator in your <cfelse> block (see below)

<cfinvoke component="services.jobboard" method="getRegions" returnvariable="regions" />
<cfset USREGIONquery = "false">
<cfset USREGION = "South">
<cfset querystring = "" >

<cfoutput>
	<cfif FindNoCase("Any US",USREGION)>
		<cfset querystring = "#querystring# (USREGION IS NOT NULL)" >
		<cfset USREGIONquery = "true">
	<cfelse>
		<cfloop query="regions">
			#usregion#-#regions.description#<br>
			#querystring#<br>
			<cfif FindNoCase(regions.description,USREGION)>
				<cfif USREGIONquery EQ "true">
					<cfset querystring = "#querystring# or (USREGION Like '%#regions.description#%')" >
			#querystring#<br>
				<cfelse>
					<!--- Should have "AND, OR, or some other operator between #querystring# and "(" --->
					<cfset querystring = "#querystring# (USREGION Like '%#regions.description#%')" >
			#querystring#<br>
					<cfset USREGIONquery = "true">
				</cfif>
			</cfif>
		</cfloop>
		<cfif USREGIONquery EQ "true">
			<cfset querystring = "#querystring# or (USREGION Like '%All%')" >
			#querystring#<br>
		</cfif>
		<cfif querystring NEQ "">
			<cfset querystring = "(#querystring#)" />
		</cfif>
		#querystring# 
	</cfif>
</cfoutput>

Open in new window

0
 

Author Comment

by:lanterv
ID: 39788926
<cfif USREGIONquery EQ "true">
means that the query string has already been added to and an and/or is needed.

The findnocase issue is a good one, but the original code didn't use it and worked fine.  I have made the change anyway but it didn't help.
0
 
LVL 12

Expert Comment

by:mkishline
ID: 39788999
In the list of the output that you posted:
South-New England
(South-Northeast
(South-Mid Atlantic
(South-Southeast
(South-South
(South-Great Lakes
(South-Midwest
(South-South Central
(South-Mountain States
(South-Southern California
(South-Northern California
(South-Southwest
(South-Northwest

Is the idea that the desired list should only contain the records whose regions.description contains "South"?
Desired resultset:
South-Southeast, South-South, South-South Central, South-Southern California, South-Southwest

If so, you should switch the arguments you are sending to the find function:
<cfif Find(USREGION, regions.description)>

Open in new window


If I have misidentified what your expected result set should look like, can you provide more detail as to what your desired results would contain?
0
 

Author Comment

by:lanterv
ID: 39789000
I haven't figured out why yet, but the code is working now with the findnocase changes.  Thank you.
0

Featured Post

Resolve Critical IT Incidents Fast

If your data, services or processes become compromised, your organization can suffer damage in just minutes and how fast you communicate during a major IT incident is everything. Learn how to immediately identify incidents & best practices to resolve them quickly and effectively.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Coldfusion CFMESSAGEBOX Passing Variables 6 127
cfchart display 12 104
what type of join should I use on these 2 queries 9 103
Combining Queries 7 28
The technique is by far very Simple! How we can export the ColdFusion query results to DOC file?  Well before writing this I researched a lot in Internet but did not found a good Answer anyways!  So i thought now i should share my small snippet w…
Hi, I will be creating today a basic tutorial on how we can create a Mail Custom Function and use it where ever we want. The main advantage about creating a custom function is that we can accommodate a range of arguments to pass to the Function and …
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

861 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question