Copy link to clipboard
Copied
I have a query which I try to fetch records using IN operator. But the record retrieves only first data not all data . What I have tried is
<cfargument name="names" type="struct" >
SELECT
ID,
Name,
Title
FROM
tblabcd
WHERE
Active = 1
AND Name IN(<cfqueryparam value="#arguments.names.data#" cfsqltype="cf_sql_varchar" list="yes" separator="|">)
</cfquery>
Here arguments.names.data = abc data | efg name
Query return only data of "abc data"
How to return both data's from this query using IN operator.
Copy link to clipboard
Copied
What happens if you just put in "efg name" as a test value in place of the CFQUERYPARAM? Do you get any matching records?
Dave Watts, Eidolon LLC
Copy link to clipboard
Copied
Yes
Copy link to clipboard
Copied
You have to trim each list element to remove any extra space.
<!--- Before the query tag, trim each list item of arguments.names.data --->
<cfscript>
closure=function(listItem){
return trim(listItem); // Trims each list item
}
// New list without excess spaces
trimmedList=listMap(arguments.names.data,closure,"|");
</cfscript>
<!--- Within the query --->
<cfqueryparam value="#trimmedList#" cfsqltype="cf_sql_varchar" list="yes" separator="|">
Copy link to clipboard
Copied
Good catch!
Dave Watts, Eidolon LLC
Copy link to clipboard
Copied
Not Working
Copy link to clipboard
Copied
Not Working
By @abcd987
Could you tell us a bit more? What isn't working? Do you get an error? If not, what you get when you dump the query?
Copy link to clipboard
Copied
Not working
Copy link to clipboard
Copied
Here is proof that the suggestion works. Just copy-paste it and run it as a CFM page.
<!--- Simulate the query --->
<cfscript>
data = queryNew('ID,Name,Title,Active','integer,varchar,varchar,bit');
queryAddRow(data,2);
// Row 1
querySetCell(data,'ID','1',1);
querySetCell(data,'Name','abc data',1);
querySetCell(data,'Title','First Title',1);
querySetCell(data,'Active',1,1);
// Row 2
querySetCell(data,'ID','2',2);
querySetCell(data,'Name','efg name',2);
querySetCell(data,'Title','Second Title',2);
querySetCell(data,'Active',1,2);
</cfscript>
<cfset arguments.names.data=" abc data | efg name ">
<!--- Trim each list item of arguments.names.data --->
<cfscript>
closure=function(listItem){
return trim(listItem); // Trims each list item
};
// New list without excess spaces
trimmedList=listMap(arguments.names.data,closure,"|");
</cfscript>
<!--- Run the query --->
<cfquery name="activeData" dbtype="query">
SELECT
ID,
Name,
Title
FROM
data
WHERE
Active = 1
AND Name IN(<cfqueryparam value="#trimmedList#" cfsqltype="cf_sql_varchar" list="yes" separator="|">)
</cfquery>
<!--- Dump the query result --->
<cfdump var="#activeData#" label="Data for which Active=1">
The result is: