This is weird issue I am geting while doing Query of Query

Question

Here is the scenario, I am getting the result-set from the following code and i am running the following below query after getting the result from cfdump, but it shows empty second dump, do not why?

can anyone check what is wrong:

<cfif StructKeyExists(URL,'submitsearch') AND URL.submitsearch neq ''>
  <cfset answers = initial.getSearchResults('#URL#')>
  <cfset flag = 'yes'>
</cfif>

<cfchart format="png" scalefrom="0" scaleto="#answers.recordcount#" show3d="Yes">
    <cfchartseries type="bar" serieslabel="Support Tickets" seriescolor="##009933">
    <cfdump var="#answers#">
    <cfoutput query="answers">
        <cfquery dbtype="query" name="myString">
            SELECT count(*) as strvalue 
            FROM answers 
            WHERE status = "#trim(answers.status)#"
        </cfquery>
    <cfchartdata item="#Status#" value="#myString.strvalue#">
    </cfoutput>
    <cfdump var="#myString#">
   </cfchartseries>
   </cfchart>

This is howing an issue, it does not anything: do not know why:

<cfdump var="#myString#">

Edit

Screenshot below

enter image description here

score 1 · Accepted Answer · answered Mar 16 '14 at 05:52

1

Based on the screenshot provided, the following is suggested

<cfif StructKeyExists(URL,'submitsearch') AND URL.submitsearch neq ''>
  <cfset answers = initial.getSearchResults(URL)>
  <cfset flag = 'yes'>
</cfif>


<cfquery dbtype="query" name="qrySummary">
    SELECT status, count(status) as strvalue 
    FROM   answers 
    GROUP BY status
    ORDER BY status
</cfquery>

<cfdump var="#qrySummary#">


<cfchart format="png" scalefrom="0" scaleto="#answers.recordcount#" show3d="Yes">
  <cfchartseries type="bar" serieslabel="Support Tickets" seriescolor="##009933">
  <cfoutput query="qrySummary">
      <cfchartdata item="#Status#" value="#strvalue#">
  </cfoutput>
  </cfchartseries>
</cfchart>

Also see

Query of query support for aggregate functions

http://help.adobe.com/en_US/ColdFusion/9.0/Developing/WSc3ff6d0ea77859461172e0811cbec0e4fd-7ff0.html#WSc3ff6d0ea77859461172e0811cbec0e4fd-7fcc

answered Mar 16 '14 at 05:52

James A Mohler

11,060
15
46
72

Thanks, It solved the Issue, but i want to know why QoQ did not worked inside the cfloop or cfoutput, ignore cfchart option. i had did that above many times, but why did not worked this time and in coldfusion 10, specifically – Regual Mar 16 '14 at 05:54
I suspect that because you were in a `` powered by answers AND there was a QoQ powered by the same variable, it did no know which was which (Or it just picked the wrong one) – James A Mohler Mar 16 '14 at 06:01
Thanks, if any other experts have some idea, please drop by and explain me this, so i can understand it well. regards – Regual Mar 16 '14 at 06:09

James A Mohler · Answer 2 · 2014-03-16T05:34:50.087

0

Copying structs

First off

<cfset answers = initial.getSearchResults('#URL#')>

Won't work. If we ignore the dangers with this, you can

<cfset answers = initial.getSearchResults(URL)>

<cfdump> can't generate any output inside of a <cfchart>. If you want to a dump, it has to be outside of the chart. To debug your data consider using something like this:

<cfdump var="#answers#">
<cfoutput query="answers">
    <cfquery dbtype="query" name="myString">
        SELECT count(*) as strvalue 
        FROM answers 
        WHERE status = <cfqueryparam value = "trim(answers.status)#" cfsqltype = "cf_sql_varchar">
    </cfquery>
    #myString.strvalue# 
</cfoutput>
<cfdump var="#myString#">

Also consider, changing code so that you don't have have an inner query. You will get better performance and it will be easier to debug.

UPDATE

<cfdump var="#answers#">
<!--- at this point, confirm that you are returning a query --->


<cfoutput query="answers">
    <cfquery dbtype="query" name="myString">
        SELECT count(status) as strvalue 
        FROM   answers 
        WHERE  status = <cfqueryparam value = "trim(answers.status)#" cfsqltype = "cf_sql_varchar">
    </cfquery>

    <!--- here you should have a series of queries with one row, one column --->
    <cfdump var="#myString#">
</cfoutput>

edited Mar 16 '14 at 05:34

answered Mar 15 '14 at 19:20

James A Mohler

11,060
15
46
72

i changed the above as you said, but now i am getting variable answers in undefined. It is really getting frustrated even through the answers has a full sql inside in it, why it is saying it is undefined. I am using coldfusion10. – Regual Mar 16 '14 at 03:50
Your question really needs to have the code for `initial.getSearchResults` – James A Mohler Mar 16 '14 at 04:47
should i paste the resultset for the initial.getSearchResults – Regual Mar 16 '14 at 05:30
Just the code for `initial.getSearchResults`. It might be returning something that you are not expecting – James A Mohler Mar 16 '14 at 05:32
http://screencast.com/t/cJq3YSNAVbIU. Check this screenshot, it has resultset returning – Regual Mar 16 '14 at 05:36

This is weird issue I am geting while doing Query of Query

2 Answers2