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

60

Likes

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>

Likes

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
Adobe Community Professional ,
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;
}

 

Likes

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

Copy link to clipboard

Copied

How did things turn out?


/Charlie (server troubleshooter, carehart.org)

Likes

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
Adobe Community Professional ,
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. 

Likes

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