coldfusion - Railo Query of Query 返回错误结果

Question

我正在运行以下两个语句:

首先是 A) 它做它需要做的事情并工作:

SELECT
  itemColumn
 ,valueColumn
 ,label
 FROM
 rstCombinedChartData

 UNION ALL

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

这给了我以下结果:



接下来我需要获取这些结果并在这种情况下为每个值取回 itemcolumn 的总数是和否，即

i.e.
Yes 200
No 400 

B)这是我必须实现的查询:

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

但是，我得到以下不正确的结果:



查询 B 发生了什么，它应该是是 = x 和否 = x，而不是我得到错误和所有总数？

CF功能:

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

好的 - 主要更新

我不知道为什么，但我错误地添加了这一行:

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

突然之间，查询开始在 Railo 中工作!好的，现在我有一个额外的结果，它没有意义“1”但是 WTF!似乎 Railo 不喜欢所有是的，没有答案我把其他东西混合在一起，它开始像 varchars 那样正确地再次正确地踩它。

有谁知道这里发生了什么？我已经厌倦了将 sql 转换为 varchar 但这不仅在 CF 级别起作用，而且会发生某些事情。



如果我拿出线:

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

它可以追溯到:

Answer 1

从评论中 - 我很好奇为什么你必须在 valueColumn 行中将 CAST(valueColumn AS INTEGER) AS valueColumn 转换为整数，当它已经是一个整数时？

您应该尝试将 itemColumn 转换为同一 SQL 语句中的字符。注意:在 MySQL 中，您不能转换为 VARCHAR。在 MySQL 中，您必须使用 CHAR。类似 CAST(itemColumn AS CHAR) AS itemColumn 的东西。

其余的不是真正的答案，但评论太长了

我根据您共享的数据创建了一个独立的再现，但我不能让它失败。您的查询在 ACF 和 Railo 中对我来说是正确的。复制下面的代码并将其粘贴到 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>

您可以在 cfdump 的 rstChartDataTotal 结果中看到 Railo 将 itemColumn 维护为 varchar 并且仍然显示"is"或“否”。



在 Adob​​e ColdFusion 中，它将 itemColumn 更改为 bool 值并显示为“true”或“false”。



我相信异常仅来自在 cflive.net 生成的输出，但在这两种情况下，查询仍然运行并正确计算了两列的结果？