Railo Query of Query returning incorrect results

Question

I'm running the following two statements:

First is A) which does what it needs to do and works:

SELECT
  itemColumn
 ,valueColumn
 ,label
 FROM
 rstCombinedChartData

 UNION ALL

 SELECT
 itemColumn
 ,CAST(valueColumn AS INTEGER) AS valueColumn
 ,label
 FROM
 rstChartData  

This gives me the following results:

Next I need to take these results and get back total of itemcolumn for each value in this case yes and no i.e.

i.e.
Yes 200
No 400 

B) This is the query I have for to achieve this:

SELECT
itemColumn
,SUM(valueColumn) AS valueColumn
,label
FROM
rstCombinedChartData (this is above result set)
GROUP BY
label
,itemColumn
ORDER BY
label DESC
,itemColumn DESC

However, I get the following result which is not correct:

Whats going on here with query B it should be yes = x and no = x, instead I'm getting false and all the totals?

CF function:

    <cffunction name="getAverageChartData" hint="I return the data required to render an average chart." returntype="array" output="false">
        <cfargument name="surveyList" hint="I am a record set of Surveys." required="true" type="query" />
        <cfargument name="filter" hint="I am the optional filter which is to be applied to all results." required="false" default="" type="string" />

        <cfset var local=structNew() />

        <cfset var rstChartData="" />
        <cfset var rstChartDataTotal="" />
        <cfset var rstCombinedChartData=queryNew("itemColumn,valueColumn,label","varchar,integer,varchar") />

        <cfset local.objQuestion=objQuestionService.get(arguments.surveyList.question_ID[1]) />
        <cfset local.intQuestionTypeID = local.objQuestion.getTypeID() />

        <cfset local.strSubQuestionList=local.objQuestion.getAnswer() />
        <cfset local.strPossibleAnswerList=local.objQuestion.getPossibleAnswer() />

        <cfset local.arrChartDataResult=arrayNew(1) />

<!--- loop over each school's survey --->
        <cfloop query="arguments.surveyList">
            <cfset local.arrChartData = getChartData(arguments.surveyList.survey_id, arguments.surveyList.question_id, arguments.filter) />

<!--- loop over each sub question and append (union) it to a running total --->
            <cfloop array="#local.arrChartData#" index="rstChartData">
                <cfquery name="rstCombinedChartData" dbtype="query">
                    SELECT
                         itemColumn
                        ,valueColumn
                        ,label
                    FROM
                        rstCombinedChartData

                    UNION ALL

                    SELECT
                         itemColumn
                        ,CAST(valueColumn AS INTEGER) AS valueColumn
                        ,label
                    FROM
                        rstChartData
                </cfquery>
            </cfloop>
        </cfloop>


<!--- get the totals for each itemColumn --->
            <cfquery name="rstChartDataTotal" dbtype="query">
                SELECT
                     itemColumn
                    ,SUM(valueColumn) AS valueColumn
                    ,label
                FROM
                    rstCombinedChartData
                GROUP BY
                     label
                    ,itemColumn
                ORDER BY
                     label DESC
                    ,itemColumn DESC
            </cfquery>

OK - Major UPDATE

I have no idea why but I added this line in by mistake:

<cfset querySetCell(rstCombinedChartData, "itemColumn", "1") />

All of a sudden the query started to work in Railo! Ok now I have one extra result that makes no sense "1" but WTF! It seems that Railo does not like all yes, no answers I put something else into the mix and it starts to tread it correctly again as varchars like it should.

anyone know whats going on here? I have tired casting in sql as varchar but this does not work only at the CF level does something happen.

If i take out the line:

<cfset querySetCell(rstCombinedChartData, "itemColumn", "1") />

it goes back to:

Answer 1

From the comments - I am curious why you had to CAST the valueColumn as an integer in the line CAST(valueColumn AS INTEGER) AS valueColumn when it is already an integer?

You should try casting your itemColumn as a char in the same SQL statement. Note: in MySQL you cannot cast to VARCHAR. In MySQL you must use CHAR. Something like CAST(itemColumn AS CHAR) AS itemColumn.

The rest of this is not really an answer but was too long for a comment

I created a self-contained repro based on the data that you shared but I cannot get it to fail. Your query works correctly for me in ACF and Railo. Copy the code below and paste it into cflive.net.

<cftry>
<cfset rstCombinedChartData = QueryNew("itemColumn,valueColumn,label","varchar,integer,varchar") />
<cfset temp = QueryAddRow(rstCombinedChartData) />
<cfset temp = QuerySetCell(rstCombinedChartData, "itemColumn", "Yes") />
<cfset temp = QuerySetCell(rstCombinedChartData, "valueColumn", 33) />
<cfset temp = QuerySetCell(rstCombinedChartData, "label", "") />
<cfset temp = QueryAddRow(rstCombinedChartData) />
<cfset temp = QuerySetCell(rstCombinedChartData, "itemColumn", "No") />
<cfset temp = QuerySetCell(rstCombinedChartData, "valueColumn", 45) />
<cfset temp = QuerySetCell(rstCombinedChartData, "label", "") />
<cfset temp = QueryAddRow(rstCombinedChartData) />
<cfset temp = QuerySetCell(rstCombinedChartData, "itemColumn", "Yes") />
<cfset temp = QuerySetCell(rstCombinedChartData, "valueColumn", 0) />
<cfset temp = QuerySetCell(rstCombinedChartData, "label", "") />
<cfset temp = QueryAddRow(rstCombinedChartData) />
<cfset temp = QuerySetCell(rstCombinedChartData, "itemColumn", "No") />
<cfset temp = QuerySetCell(rstCombinedChartData, "valueColumn", 0) />
<cfset temp = QuerySetCell(rstCombinedChartData, "label", "") />
<cfset temp = QueryAddRow(rstCombinedChartData) />
<cfset temp = QuerySetCell(rstCombinedChartData, "itemColumn", "Yes") />
<cfset temp = QuerySetCell(rstCombinedChartData, "valueColumn", 72) />
<cfset temp = QuerySetCell(rstCombinedChartData, "label", "") />
<cfset temp = QueryAddRow(rstCombinedChartData) />
<cfset temp = QuerySetCell(rstCombinedChartData, "itemColumn", "No") />
<cfset temp = QuerySetCell(rstCombinedChartData, "valueColumn", 66) />
<cfset temp = QuerySetCell(rstCombinedChartData, "label", "") />
<cfset temp = QueryAddRow(rstCombinedChartData) />
<cfset temp = QuerySetCell(rstCombinedChartData, "itemColumn", "Yes") />
<cfset temp = QuerySetCell(rstCombinedChartData, "valueColumn", 0) />
<cfset temp = QuerySetCell(rstCombinedChartData, "label", "") />
<cfset temp = QueryAddRow(rstCombinedChartData) />
<cfset temp = QuerySetCell(rstCombinedChartData, "itemColumn", "No") />
<cfset temp = QuerySetCell(rstCombinedChartData, "valueColumn", 0) />
<cfset temp = QuerySetCell(rstCombinedChartData, "label", "") />
<cfset temp = QueryAddRow(rstCombinedChartData) />
<cfset temp = QuerySetCell(rstCombinedChartData, "itemColumn", "Yes") />
<cfset temp = QuerySetCell(rstCombinedChartData, "valueColumn", 42) />
<cfset temp = QuerySetCell(rstCombinedChartData, "label", "") />
<cfset temp = QueryAddRow(rstCombinedChartData) />
<cfset temp = QuerySetCell(rstCombinedChartData, "itemColumn", "No") />
<cfset temp = QuerySetCell(rstCombinedChartData, "valueColumn", 38) />
<cfset temp = QuerySetCell(rstCombinedChartData, "label", "") />
<cfset temp = QueryAddRow(rstCombinedChartData) />
<cfset temp = QuerySetCell(rstCombinedChartData, "itemColumn", "Yes") />
<cfset temp = QuerySetCell(rstCombinedChartData, "valueColumn", 64) />
<cfset temp = QuerySetCell(rstCombinedChartData, "label", "") />
<cfset temp = QueryAddRow(rstCombinedChartData) />
<cfset temp = QuerySetCell(rstCombinedChartData, "itemColumn", "No") />
<cfset temp = QuerySetCell(rstCombinedChartData, "valueColumn", 83) />
<cfset temp = QuerySetCell(rstCombinedChartData, "label", "") />
<cfset temp = QueryAddRow(rstCombinedChartData) />
<cfset temp = QuerySetCell(rstCombinedChartData, "itemColumn", "Yes") />
<cfset temp = QuerySetCell(rstCombinedChartData, "valueColumn", 0) />
<cfset temp = QuerySetCell(rstCombinedChartData, "label", "") />
<cfset temp = QueryAddRow(rstCombinedChartData) />
<cfset temp = QuerySetCell(rstCombinedChartData, "itemColumn", "No") />
<cfset temp = QuerySetCell(rstCombinedChartData, "valueColumn", 0) />
<cfset temp = QuerySetCell(rstCombinedChartData, "label", "") />
<cfset temp = QueryAddRow(rstCombinedChartData) />
<cfset temp = QuerySetCell(rstCombinedChartData, "itemColumn", "Yes") />
<cfset temp = QuerySetCell(rstCombinedChartData, "valueColumn", 65) />
<cfset temp = QuerySetCell(rstCombinedChartData, "label", "") />
<cfset temp = QueryAddRow(rstCombinedChartData) />
<cfset temp = QuerySetCell(rstCombinedChartData, "itemColumn", "No") />
<cfset temp = QuerySetCell(rstCombinedChartData, "valueColumn", 43) />
<cfset temp = QuerySetCell(rstCombinedChartData, "label", "") />
<cfset temp = QueryAddRow(rstCombinedChartData) />
<cfset temp = QuerySetCell(rstCombinedChartData, "itemColumn", "Yes") />
<cfset temp = QuerySetCell(rstCombinedChartData, "valueColumn", 0) />
<cfset temp = QuerySetCell(rstCombinedChartData, "label", "") />
<cfset temp = QueryAddRow(rstCombinedChartData) />
<cfset temp = QuerySetCell(rstCombinedChartData, "itemColumn", "No") />
<cfset temp = QuerySetCell(rstCombinedChartData, "valueColumn", 0) />
<cfset temp = QuerySetCell(rstCombinedChartData, "label", "") />
<cfquery name="rstChartDataTotal" dbtype="query">
    SELECT
        itemColumn
        ,SUM(valueColumn) AS valueColumn
        ,label
    FROM
        rstCombinedChartData
    GROUP BY
        label
        ,itemColumn
    ORDER BY
        label DESC
        ,itemColumn DESC
</cfquery>
<html>
    <head><title>Test</title></head>
    <body>
        <h3>Test</h3>
        <div>
            <cfoutput query="rstChartDataTotal">
            <p>#rstChartDataTotal.itemColumn# - #rstChartDataTotal.valueColumn#</p>
            </cfoutput>
            <cfdump var="#rstCombinedChartData#" label="rstCombinedChartData" />
            <hr />
            <cfdump var="#rstChartDataTotal#" label="rstChartDataTotal" />
        </div>
    </body>
</html>
<cfcatch type="any">
    <cfdump var="#cfcatch#" />
</cfcatch>
</cftry>

You can see in the cfdump results of rstChartDataTotal that Railo maintains the itemColumn as a varchar and still displays "yes" or "no".

In Adobe ColdFusion it changes the itemColumn to a boolean and displays as "true" or "false".

I believe that anomaly is only coming from the output generated at cflive.net, but in both cases the query still ran and correctly calculated the results of both columns?

Answer 2

This may be a scoping/ reference issue;

Change:

<cfquery name="rstCombinedChartData" dbtype="query">

Into

<cfquery name="local.q" dbtype="query">

Then after the QoQ, at the end of the local.arrChartData loop, set it back to the rstCombinedChartData variable;

        <cfloop array="#local.arrChartData#" index="rstChartData">
            <cfquery name="local.q" dbtype="query">
                SELECT
                     itemColumn
                    ,valueColumn
                    ,label
                FROM
                    rstCombinedChartData

                UNION ALL

                SELECT
                     itemColumn
                    ,CAST(valueColumn AS INTEGER) AS valueColumn
                    ,label
                FROM
                    rstChartData
            </cfquery>
            <cfset rstCombinedChartData = local.q />
        </cfloop>
    </cfloop>

This should make sure all pointers/ references are set correctly.

Answer 3

Try this query -

SELECT UNIQUE itemColumn
      , SUM( SELECT valueColumn 
             FROM   rstCombinedChartData 
             WHERE itemColumn = "yes") as YES
      , SUM ( SELECT valueColumn 
             FROM   rstCombinedChartData 
             WHERE  itemColumn = "no") as NO
      , label;