Webmaster Forum / ColdFusion / Getting Started / January 2008

Why on earth would you try to create an alias for a parameter in a WHERE
clause? What are you expecting to do with this alias?

Also, [b]where Referer like '%google%' and Referer like '%msn%'[/b] would only
return rows where Referer contained both google and msn in the same row.

Phil

Hi Phil,
   I am going to use the info for a cfchart but that is by the by really.

What I am going to do to output all the values from the query. <cfoutput
query="qrysales">#qrysales.referer#</cfoutput>

What I was doing before was this:
<cfoutput query="qrysales">
<cfif find("google.it", qrysales.referer)>
      Google Italy
<cfelseif find("google", qrysales.referer)>
      Google
<cfelseif find("msn", qrysales.referer) or find("live.com", qrysales.referer)>
      MSN Live
<cfelseif find("yahoo", qrysales.referer)>
      Yahoo
<cfelseif qrysales.referer does not contain "google" or qrysales.referer does
not contain "yahoo"
 or qrysales.referer does not contain "msn">
      Other search engine
      </cfif>

but this isn?t needed now. What I want to do is set in the sql these
conditions to variables/values. I always understood that it is best to do
everything in the sql. Please advise as this is confusing.

I?m going to look at query of a query again I think this is the only way to do this

[Q]I always understood that it is best to do everything in the sql.[/Q] Not
necessarily. Also, nothing is ever "returned" from the WHERE clause of a query,
only the SELECT. If you have a small set of posible values that will be
matching in your query, you may be able to use a CASE statement in your SELECT,
but that can get real nasty, especially as you add values to your Referer
table, since that would require you to modify your query, which is NOT a
desireable circumstance.

Something like this, perhaps.

SELECT
 CASE
    WHEN referer LIKE '%google.it%' THEN 'Google Italy'
    WHEN referer LIKE '%google'% THEN 'Google'
    WHEN referer LIKE '%msn%' OR referer LIKE '%live.com%' THEN 'MSN Live'
    WHEN referer LIKE '%yahoo%' THEN 'Yahoo'
    ELSE 'Other search engine'
 END AS new_referer
FROM    clientinfo
WHERE    whatever

Phil

Thanks that is some pretty hefty sql. So either I get into some sql trouble or
look at query of query? I am going to use the case solution for the time being.
Excuse my ignorance but what is the other way so I can study how to do it?

Other way: OK so then I can set those values to variables like this:

<cfoutput query="qrysales">
<cfif find("google.it", qrysales.referer)>
Google Italy <cfset googleit=qrysales.referer>
<cfelseif find("google", qrysales.referer)>
Google <cfset google=qrysales.referer>
<cfelseif find("msn", qrysales.referer) or find("live.com", qrysales.referer)>
MSN Live <cfset msn=qrysales.referer>
<cfelseif find("yahoo", qrysales.referer)>
Yahoo <cfset yahoo=qrysales.referer>
<cfelseif qrysales.referer does not contain "google" or qrysales.referer does
not contain "yahoo"
or qrysales.referer does not contain "msn">
Other search engine <cfset otherse=qrysales.referer>
</cfif>
</cfoutput>

How do I get a "record count" of the newly set variables? len(googleit) give
me the chars in each record not the number of records?