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

cfscript query of queries not working with where clause

New Here ,
Mar 02, 2021 Mar 02, 2021

trying to get query of queries to work in script. It wil not work when where clause is added.

I have a function and i pass in a query and trying to query that passed in query.

it works when i take out the where clause: cannot find any docs that specify the where clause being written differently.  thanks.

function(query orgData){

adminUsersQuery = new Query();
adminUsersQuery.setDBType('query');
adminUsersQuery.setAttributes(sourceQuery=orgData);
adminUsersQuery.addParam(name='region', value='midwest', cfsqltype='cf_sql_varchar');
adminUsersQuery.setSQL("SELECT distinct aoi FROM sourceQuery where region = :region");
 newOrgData = adminUsersQuery.execute().getResult();

}

281
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
Participant ,
Mar 03, 2021 Mar 03, 2021

I created the follow sample script to run your code and it works as expected for me. Are you seeing any particular error?

 

<cfscript>
mq = queryNew("aoi,region","Integer,Varchar", 
[ 
{aoi=1,region="southeast"}, 
{aoi=2,region="northeast"}, 
{aoi=3,region="midwest"} 
]);	

runQuery(mq);
	
function runQuery(query orgData){
adminUsersQuery = new Query();
adminUsersQuery.setDBType('query');
adminUsersQuery.setAttributes(sourceQuery=orgData);
adminUsersQuery.addParam(name='region', value='midwest', cfsqltype='cf_sql_varchar');
adminUsersQuery.setSQL("SELECT distinct aoi FROM sourceQuery where region = :region");
newOrgData = adminUsersQuery.execute().getResult();

writeDump(newOrgData);
}
</cfscript>
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 ,
Mar 05, 2021 Mar 05, 2021

1. Give function a name;

2. Use QueryExecute() instead. It is faster than Query(), and has simpler code:

function getQueryOfQuery(query orgData){

	var sqlParams = {};
	var sqlString = "SELECT distinct aoi FROM sourceQuery where region = :region";
	
	sqlParams.region = {value="midwest", cfsqltype="varchar"};
	
	var newOrgData = queryExecute(sqlString, sqlParams, {dbType="query"});
	
	return newOrgData;
}

 

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 ,
Mar 11, 2021 Mar 11, 2021

How did things turn out?


/Charlie (troubleshooter, carehart. org)
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 ,
Mar 12, 2021 Mar 12, 2021
LATEST

Hi @muncho62 ,

Could you please give us some feedback. It will help others having a similar issue.

Your title, "cfscript query of queries not working with where clause", is of the kind that will certainly receive hits in future. 

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