获取查询查询错误 [英] Getting Queries of Queries error

问题描述

我正在使用以下代码，想知道为什么会出现 Queries of Queries错误？

I am using the following code and wondering why I am getting "Queries of Queries" error?

<cfquery name="findpercentage" datasource="#mydatasource#">

    SELECT  
         Count(TableId_bi) AS Total_Events
            ,Sum(CASE WHEN 'OPEN'       =   Event_vch THEN 100 END) / Count(*) AS OPENS
            ,Sum(CASE WHEN 'BOUNCE'     =   Event_vch THEN 100 END) / Count(*) AS BOUNCE
            ,Sum(CASE WHEN 'DEFERRED'   =   Event_vch THEN 100 END) / Count(*) AS DEFERRED
            ,Sum(CASE WHEN 'DROPPED'    =   Event_vch THEN 100 END) / Count(*) AS DROPPED
            ,Sum(CASE WHEN 'DELIVERED'  =   Event_vch THEN 100 END) / Count(*) AS DELIVERED
            ,Sum(CASE WHEN 'PROCESSED'  =   Event_vch THEN 100 END) / Count(*) AS PROCESSED
            ,Sum(CASE WHEN 'SPAMREPORT' =   Event_vch THEN 100 END) / Count(*) AS SPAMREPORT 
    FROM    
            mydatabase;


    </cfquery>


<cfdump var="#findpercentage#">



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

SELECT OPENS as Ecount, 'Open' as type
from findpercentage

UNION

SELECT BOUNCE as Ecount, 'Bounce' as type
from findpercentage

UNION

SELECT DEFERRED as Ecount, 'Deferred' as type
from findpercentage

UNION

SELECT DROPPED as Ecount, 'Dropped' as type
from findpercentage

UNION

SELECT DELIVERED as Ecount, 'Delivered' as type
from findpercentage

UNION

SELECT PROCESSED as Ecount, 'Processed' as type
from findpercentage

UNION


SELECT SPAMREPORT as Ecount, 'Spamreport' as type
from findpercentage



</cfquery>




<cfdump var="#piechartdisplay#">

查询的第一部分是将适当的内容转储到网页上，但是，第二次尝试 piechartdisplay正在生成查询或查询错误。错误描述如下：

The first part of the query is dumping propery on the webpage, however, the second dumping attempt of "piechartdisplay" is generating Query or Query error. The error description is as follows:

Error Executing Database Query.

Query Of Queries syntax error.
Encountered "DEFERRED. Incorrect Select List, Incorrect select column,

The error occurred in C:\Path\myfile.cfm: line 39

37 : 
38 : 
39 : <cfquery name = "piechartdisplay" dbtype = "query">
40 : 
41 : SELECT OPENS as Ecount, 'Open' as type
SQL    SELECT OPENS as Ecount, 'Open' as type from findpercentage UNION SELECT BOUNCE as Ecount, 'Bounce' as type from findpercentage UNION SELECT DEFERRED as Ecount, 'Deferred' as type from findpercentage UNION SELECT DROPPED as Ecount, 'Dropped' as type from findpercentage UNION SELECT DELIVERED as Ecount, 'Delivered' as type from findpercentage UNION SELECT PROCESSED as Ecount, 'Processed' as type from findpercentage UNION SELECT SPAMREPORT as Ecount, 'Spamreport' as type from findpercentage

我必须像下面这样传递来自上面查询的数据（在它开始无错运行之后），这就是为什么我这样写我的QoQ如上：

I have to pass the data from above query(after it starts running without errors) like the following, and that's why I wrote my QoQ like above:

<cfset dataItem =[
            '#type#', '#Ecount#'
        ]>

请告知我这里的错误所在。

Please advise where I am wrong here.

推荐答案

错误提示，递延是保留字（ 查询中的保留字）。将其别名化为原始查询中的其他内容，或者尝试在QoQ中使用方括号将其转义。

As implied by the error, deferred is a reserved word ("Reserved words in queries"). Alias it as something else in your original query, or try escaping it with square brackets in the QoQ.

这篇关于获取查询查询错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！