cfscript query of queries not working with where clause

[muncho62]

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

}

[John123]

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>

[BKBK]

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

[Charlie Arehart]

How did things turn out?

[BKBK]

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.