• Global community
    • Language:
      • Deutsch
      • English
      • Español
      • Français
      • Português
  • 日本語コミュニティ
    Dedicated community for Japanese speakers
  • 한국 커뮤니티
    Dedicated community for Korean speakers
Exit
0

How to get all records using IN operator in cfqueryparam in coldfusion?

New Here ,
Dec 29, 2022 Dec 29, 2022

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.

Views

237

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Expert ,
Dec 29, 2022 Dec 29, 2022

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 

Dave Watts, Eidolon LLC

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
New Here ,
Dec 29, 2022 Dec 29, 2022

Copy link to clipboard

Copied

Yes

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Expert ,
Dec 29, 2022 Dec 29, 2022

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="|">

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Expert ,
Dec 29, 2022 Dec 29, 2022

Copy link to clipboard

Copied

Good catch!

 

Dave Watts, Eidolon LLC 

Dave Watts, Eidolon LLC

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
New Here ,
Dec 29, 2022 Dec 29, 2022

Copy link to clipboard

Copied

Not Working

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Expert ,
Dec 30, 2022 Dec 30, 2022

Copy link to clipboard

Copied

quote

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?

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
New Here ,
Dec 29, 2022 Dec 29, 2022

Copy link to clipboard

Copied

Not working

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Expert ,
Dec 30, 2022 Dec 30, 2022

Copy link to clipboard

Copied

LATEST

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:

BKBK_0-1672393991428.png

 

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Resources
Documentation