I think you can significantly simplify your WHERE clause be losing some
"vacancies." and some parens. Try:
WHERE
archive = <cfqueryparam cfsqltype="cf_sql_bit" value="no">
<cfif Isdefined('divisionID') AND divisionID NEQ 'All'>AND vdivisionID =
<cfqueryparam cfsqltype="cf_sql_integer" value="#divisionID#"></cfif>
<cfif IsDefined('vtype')>AND vtype = <cfqueryparam cfsqltype="cf_sql_clob"
value="#vtype#"></cfif>
<cfif Isdefined('keyWords') AND keywords neq 'ALL' AND keywords neq ''>
AND
(
postitle LIKE <cfqueryparam cfsqltype="cf_sql_clob" value="%#Keywords#%"> OR
freetext LIKE <cfqueryparam cfsqltype="cf_sql_clob" value="%#keyWords#%"> OR
actlocation LIKE <cfqueryparam cfsqltype="cf_sql_clob" value="%#keyWords#%">
OR
vacdescrip LIKE <cfqueryparam cfsqltype="cf_sql_clob" value="%#keyWords#%">
OR
benefits LIKE <cfqueryparam cfsqltype="cf_sql_clob" value="%keywords%">
)
</cfif>
On Wed, Nov 26, 2008 at 7:10 AM, Jason Congerton <[EMAIL PROTECTED]
> wrote:
> Hi
>
> The query below returns the wrong results when the #keywords# criteria is
> used.
>
> The query should return no more than 220 live vacancies, as all over
> vacancies have been archived. However, if you put in the #keywords# criteria
> other than all, the archive = no is completely ignored and 840 vacancies are
> returned.
>
> Please see query below, i think my brackets are in the right places. Not
> sure on this one??
>
>
> <cfquery name="getVacs" datasource="#application.dsn#">
> SELECT vacancies.vacid, vacancies.archive, vacancies.vdivisionid,
> vacancies.vtype, vacancies.postitle, vacancies.freetext,
> vacancies.actlocation, vacancies.vacdescrip, vacancies.benefits,
> vacancies.vuserid, vacancies.salary, vacancies.advertdate,
> vacancies.archivedate, vacancies.mailout, vacancies.sitesend,
> vacancies.viewcount, vacancies.applycount, vacancies.searchcount,
> vacancies.rate, vacancies.jobsend, vacancies.internal, vacancies.vreference,
> vacancies.vcontactEmail
> FROM vacancies
> WHERE (((vacancies.archive) = <cfqueryparam cfsqltype="cf_sql_bit"
> value="no">)
> <cfif Isdefined('divisionID') AND divisionID NEQ 'All'>AND ((vdivisionID) =
> <cfqueryparam cfsqltype="cf_sql_integer" value="#divisionID#">)</cfif>
> <cfif IsDefined('vtype')>AND ((vtype) = <cfqueryparam
> cfsqltype="cf_sql_clob" value="#vtype#">)</cfif>
> <cfif Isdefined('keyWords') AND keywords neq 'ALL' AND keywords neq ''>
> AND ((vacancies.postitle) LIKE <cfqueryparam cfsqltype="cf_sql_clob"
> value="%#Keywords#%">))
> OR (((vacancies.freetext) LIKE <cfqueryparam cfsqltype="cf_sql_clob"
> value="%#keyWords#%">)
> OR ((vacancies.actlocation) LIKE <cfqueryparam cfsqltype="cf_sql_clob"
> value="%#keyWords#%">)
> OR ((vacancies.vacdescrip) LIKE <cfqueryparam cfsqltype="cf_sql_clob"
> value="%#keyWords#%">)
> OR ((vacancies.benefits) LIKE <cfqueryparam cfsqltype="cf_sql_clob"
> value="%keywords%">)</cfif>)
> ORDER BY advertdate DESC
> </cfquery>
>
>
>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to
date
Get the Free Trial
http://ad.doubleclick.net/clk;207172674;29440083;f
Archive:
http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:315957
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
