Query Alteration with Coldfusion using Query of Queries (QoQ)

Using alteration in ColdFusion query we can be altered without effecting the identification of a cached query with QOQ.

Original Query Output.



After Alteration Query Output.

	<!---Start: Original Query Output--->
	<cfquery name="GetAnswerDetails" datasource="10.26.0.40">
	SELECT t1.batchId_int,t1.quesId_int,upper(t2.input_vch) as input_vch,t1.type,t1.optionCount_int,t1.text_vch,count(*) as TotalAnswer
	FROM ymsReport.BatchQuestions t1
	join ymsReport.UsersReply t2
	on t1.batchId_int = t2.batchId_bi
	and t1.quesId_int = t2.pId_int
	and t1.text_vch <> ''
	where `type` in ('SHORTANSWER','ONESELECTION')
	group by t1.batchId_int,t1.quesId_int,t2.input_vch,t1.type,t1.optionCount_int,t1.text_vch limit 0,29;
	</cfquery>
	<table border="1">
	<tr>
	<td>Question ID</td>
	<td>Input Answer</td>
	<td>Count Answer</td>
	<td>Answer Options</td>
	</tr>
	<cfloop query="GetAnswerDetails">
	<cfoutput>
	<tr>
	<td>
	#quesId_int#
	</td>
	<td>
	#input_vch#<br />
	</td>
	<td>
	#TotalAnswer#
	</td>
	<td>#optionCount_int#</td>
	</tr>
	</cfoutput>
	</cfloop>
	</table>
	<!---End: Original Query Output--->
	<br />
	<br />
	<br />
	<!---Start: Query alteration --->
	<cfset list3 = "A,B,C" />
	<cfset list4 = "A,B,C,D" />
	<cfset list5 = "A,B,C,D,E" />
	<cfset tempcount = 1/>
	<cfloop query="GetAnswerDetails">
	<cfif optionCount_int eq 4 and ListFind(list4, GetAnswerDetails.input_vch) eq 0 >
	<cfset GetAnswerDetails["input_vch"][tempcount] = "D" />
	<cfelseif optionCount_int eq 5 and ListFind(list5, GetAnswerDetails.input_vch) eq 0>
	<cfset GetAnswerDetails["input_vch"][tempcount] = "E" />
	<cfelseif optionCount_int eq 3 and ListFind(list3, GetAnswerDetails.input_vch) eq 0>
	<cfset GetAnswerDetails["input_vch"][tempcount] = "C" />
	</cfif>
	<cfset tempcount = tempcount + 1/>
	</cfloop>
	<!---End: Query alteration --->
	<!---Start: Query alteration display results--->
	<cfquery name="test" dbtype="query">
	SELECT batchId_int,quesId_int,input_vch,type,optionCount_int,text_vch,sum(TotalAnswer) as TotalAnswer
	FROM GetAnswerDetails
	group by batchId_int,quesId_int,input_vch,type,optionCount_int,text_vch;
	</cfquery>
	<table border="1">
	<tr>
	<td>Question ID</td>
	<td>Input Answer</td>
	<td>Count Answer</td>
	<td>Answer Options</td>
	</tr>
	<cfloop query="test">
	<cfoutput>
	<tr>
	<td>
	#quesId_int#
	</td>
	<td>
	#input_vch#<br />
	</td>
	<td>
	#TotalAnswer#
	</td>
	<td>#optionCount_int#</td>
	</tr>
	</cfoutput>
	</cfloop>
	</table>
	<!---End: Query alteration display results--->
	<!---Start: Now do what you want to do with Query--->
	<cfset resultArray = ArrayNew(1) />
	<cfset counter = 1 />
	<cfoutput query="test" group="quesId_int">
	<cfset resultArray[counter] = Structnew() />
	<cfset resultArray[counter]["QuestionText"] = "#ToString(text_vch)#" />
	<cfset resultArray[counter]['AnswerDetails'] = ArrayNew(1) />
	<cfoutput>
	<cfset tempStruct = Structnew() />
	<cfset tempStruct['QAnswer'] = test.input_vch />
	<cfset tempStruct['AnswerCount'] = test.TotalAnswer />
	<cfset ArrayAppend(resultArray[counter]['AnswerDetails'],tempStruct) />
	</cfoutput>
	<cfset counter = counter + 1 />
	</cfoutput>
	<cfdump var="#serializejson(resultArray)#">
	<!---End: Now do what you want to do with Query--->

view raw queryalteration.cfm hosted with ❤ by GitHub