dbtype=query with like

[joeEarl]

Has anyone been able to use LIKE in a select statement of a query of queries?
Doing this causes an error:

<cfquery name = "lastQuery" datasource = "Development">
SELECT * FROM tblCustomers
</cfQuery>

<cfquery name = "newQuery" dbtype = "query">

SELECT * FROM lastQuery where STFNAME LIKE '%joe%'
</cfquery>

[DixieGal]

can you not say simply
<cfquery name='newquery' dbtype='query'>
select * from tblcustomers where stfname like '%joe%'
</cfquery>

If you are trying to create a query that may or may not
perform a where clause, you can embed <cfif>s in your
query

<cfquery name='newquery' >
select * from tblcustomers <cfif isdefined('var')> where sftname like
'%#var#%'</cfif>
</cfquery>



--
Tami
aka DixieGal

**************************
The brain is a wonderful organ. It starts working the moment you get up in
the morning
and does not stop until you get into the office.
--Robert Frost
**************************

"joeEarl" <webforumsuser@macromedia.com> wrote in message
news:d02kqu$he8$1@forums.macromedia.com...
| Has anyone been able to use LIKE in a select statement of a query of
queries?
| Doing this causes an error:
|
| <cfquery name = "lastQuery" datasource = "Development">
| SELECT * FROM tblCustomers
| </cfQuery>
|
| <cfquery name = "newQuery" dbtype = "query">
|
| SELECT * FROM lastQuery where STFNA
ME LIKE '%joe%'
| </cfquery>
|

[joeEarl]

Hi Tami, Using the LIKE condition will work when I query the table directly
and use the "datasource" param. I could not get it to work when using the
"dbtype" param which I wanted to use to filter the previous query. So as a work
around I simply check to see if a name search has been submitted and make that
the first query using the "datasource" param and then use the "dbtype" to query
the results of the first query . Thanks for your suggestion. - Joe

[zoeski80]

Hi Joe

I have had a few problems wtih LIKE in QoQ also and finally worked through the
weirdnesses to get it working for me.

Findings:

1 - you need to do a NOT IS NULL check on the column name before doing the
LIKE, it doesn't like doing a LIKE on an empty column

2 - QoQ is case sensitive so use UPPER and UCase or LOWER and LCase

eg.
WHERE NOT stfname IS NULL AND UPPER(stfname) LIKE '%JOE%'

HTH

Zoe