CODEWITHSUNDEEP
mysqlcoldfusioncoldfusion-9
Jack
Jack

Reputation: 1079

Getting Queries of Queries error

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

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

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.

Upvotes: 0

Views: 125

Answers (1)

Adam Cameron
Adam Cameron

Reputation: 29870

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.

Upvotes: 5

Related Questions