I have a number of queries that look like this:
<cfquery name=get datasource=#dsn#>
SELECT co.contactID, co.clientID, co.fname, co.lname, co.businessphone,
co.title, co.buyertype, co.email, cl.company, p.projectID, p.projectname
FROM projects p RIGHT JOIN (clients cl LEFT JOIN contacts co ON cl.clientID
= co.clientID) ON cl.clientID = p.clientID
WHERE co.userID = #cookie.SMuserID#
<cfswitch expression='#list#'>
<cfcase value='all'></cfcase>
<cfcase value='inactive'>and co.active = 'no'</cfcase>
<cfcase value='active'>and co.active='yes'</cfcase>
<cfdefaultcase>and co.active='yes'</cfdefaultcase>
</cfswitch>
<cfif isDefined("cgroup")>and co.cgroup='#cgroup#'</cfif>
<cfif isDefined("alpha")>and co.lname like '#alpha#%'</cfif>
<cfif isDefined("search")>
and (co.fname like '%#form.search#%'
or co.lname like '%#form.search#%'
or co.email like '%#form.search#%'
or co.email2 like '%#form.search#%'
or co.homeaddress like '%#form.search#%'
or co.homecity like '%#form.search#%'
or co.homestate like '%#form.search#%'
or co.homecountry like '%#form.search#%'
or co.busaddress like '%#form.search#%'
or co.buscity like '%#form.search#%'
or co.busstate like '%#form.search#%'
or co.buscountry like '%#form.search#%'
or co.homephone like '%#form.search#%'
or co.businessphone like '%#form.search#%'
or cl.company like '%#form.search#%')
</cfif>
order by co.lname,co.contactID
</cfquery>
They work - both in Access and SQL 2000. Here's the question: is there a
more efficient way to do the <cfif isDefined>'s using SQL rather than CF? I
keep reading that as much as possible of the sorting should be done in the
database rather than CF......
TIA!
*************
Diana Nichols
Webmistress
http://www.lavenderthreads.com
770.434.7374
"One man's magic is another man's engineering." ---Lazarus Long
Archives: http://www.mail-archive.com/[email protected]/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
