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

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

New Here ,
Dec 29, 2022 Dec 29, 2022

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.

277
Translate
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

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
Translate
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

Yes

Translate
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

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

Good catch!

 

Dave Watts, Eidolon LLC 

Dave Watts, Eidolon LLC
Translate
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

Not Working

Translate
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
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?

Translate
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

Not working

Translate
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
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.pngexpand image

 

Translate
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