Hi,

I am developing a coldfusion page. I have the following query in my page:

<cfquery name = "NewApprovalQuery2" datasource = "first_products">
SELECT * from tbl_Travel
WHERE
(OrderNo is null)
AND (approved = 'no'
OR in_VFC = 'no')
AND tbl_travel.traveler like
(SELECT tbl_names.[LastName] FROM tbl_names
WHERE LastName = '#GetAuthUser()#')

ORDER BY

<cfswitch expression="#sort#">
<cfcase value="1">traveler, program</cfcase>
<cfcase value="2">traveler desc, program</cfcase>
<cfcase value="3">program</cfcase>
<cfcase value="4">program desc</cfcase>
<cfcase value="5">start_date, traveler, program</cfcase>
<cfcase value="6">start_date desc, traveler, program</cfcase>
<cfcase value="7">city, traveler, program</cfcase>
<cfcase value="8">city desc, traveler, program</cfcase>
</cfswitch>
</cfquery>

I would like to know how to put wildcard % characters around the nested query. I tried the following, but to no avail:

<cfquery name = "NewApprovalQuery2" datasource = "first_products">
SELECT * from tbl_Travel
WHERE
(OrderNo is null)
AND (approved = 'no'
OR in_VFC = 'no')
AND tbl_travel.traveler like
%(SELECT tbl_names.[LastName] FROM tbl_names
WHERE LastName = '#GetAuthUser()#')%

ORDER BY

<cfswitch expression="#sort#">
<cfcase value="1">traveler, program</cfcase>
<cfcase value="2">traveler desc, program</cfcase>
<cfcase value="3">program</cfcase>
<cfcase value="4">program desc</cfcase>
<cfcase value="5">start_date, traveler, program</cfcase>
<cfcase value="6">start_date desc, traveler, program</cfcase>
<cfcase value="7">city, traveler, program</cfcase>
<cfcase value="8">city desc, traveler, program</cfcase>
</cfswitch>
</cfquery>


Can someone help me with the syntax?


Thanks in advance,
geebee

Hi,

Use IN instead of like in Ur Query.

SELECT * from tbl_Travel
WHERE
(OrderNo is null)
AND (approved = 'no'
OR in_VFC = 'no')
AND tbl_travel.traveler IN
(SELECT tbl_names.[LastName] FROM tbl_names
WHERE LastName = '#GetAuthUser()#')

ORDER BY
<cfswitch expression="#sort#">
<cfcase value="1">traveler, program</cfcase>
<cfcase value="2">traveler desc, program</cfcase>
<cfcase value="3">program</cfcase>
<cfcase value="4">program desc</cfcase>
<cfcase value="5">start_date, traveler, program</cfcase>
<cfcase value="6">start_date desc, traveler,
program</cfcase>
<cfcase value="7">city, traveler, program</cfcase>
<cfcase value="8">city desc, traveler, program</cfcase>
</cfswitch>
</cfquery>


U can't use the Wild character '%' as u mentioned in Ur Query.

The syntax is LIKE('%');