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

cfscript query of queries not working with where clause

New Here ,
Mar 02, 2021 Mar 02, 2021

Copy link to clipboard

Copied

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();

}

Views

153

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

Copy link to clipboard

Copied

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>

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

Copy link to clipboard

Copied

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;
}

 

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

Copy link to clipboard

Copied

How did things turn out?


/Charlie (troubleshooter, carehart.org)

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

Copy link to clipboard

Copied

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. 

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