Coldfusion Query Of Queries

Question

Sql query with datasource working fine. Code ;

<cfquery name="GET_ACC_REMAINDER" datasource="#dsn2#">
    SELECT
        SUM(BAKIYE) AS BAKIYE, 
        SUM(BORC) AS BORC,
        SUM(ALACAK) AS ALACAK, 
        ACCOUNT_CODE,
        ACCOUNT_NAME,
        ACCOUNT_ID
    FROM
    (
    SELECT
        ROUND(SUM(ACCOUNT_ACCOUNT_REMAINDER.BORC - ACCOUNT_ACCOUNT_REMAINDER.ALACAK),2) AS BAKIYE, 
        ROUND(SUM(ACCOUNT_ACCOUNT_REMAINDER.BORC),2) AS BORC,
        ROUND(SUM(ACCOUNT_ACCOUNT_REMAINDER.ALACAK),2) AS ALACAK, 
        ACCOUNT_PLAN.ACCOUNT_CODE,
        ACCOUNT_PLAN.ACCOUNT_NAME,
    ACCOUNT_PLAN.ACCOUNT_ID,
    ACCOUNT_PLAN.SUB_ACCOUNT
    FROM
        (
        SELECT
            0 AS ALACAK,
             SUM(ROUND(ACCOUNT_CARD_ROWS.AMOUNT,2)) AS BORC,
            ACCOUNT_CARD_ROWS.ACCOUNT_ID    
        FROM
            ACCOUNT_CARD_ROWS,ACCOUNT_CARD
        WHERE
            BA = 0 AND ACCOUNT_CARD.CARD_ID=ACCOUNT_CARD_ROWS.CARD_ID
            AND ACTION_DATE BETWEEN  '2013-11-01 00:00:00' AND  '2013-11-30 00:00:00' GROUP BY
            ACCOUNT_CARD_ROWS.ACCOUNT_ID
        HAVING SUM(ROUND(ACCOUNT_CARD_ROWS.AMOUNT,2))<>0
    UNION ALL
        SELECT
             SUM(ROUND(ACCOUNT_CARD_ROWS.AMOUNT,2)) AS ALACAK,
            0 AS BORC,
            ACCOUNT_CARD_ROWS.ACCOUNT_ID        
        FROM
            ACCOUNT_CARD_ROWS,
            ACCOUNT_CARD
        WHERE
            BA = 1 AND ACCOUNT_CARD.CARD_ID=ACCOUNT_CARD_ROWS.CARD_ID       
            AND ACTION_DATE BETWEEN  '2013-11-01 00:00:00' AND  '2013-11-30 00:00:00' GROUP BY
            ACCOUNT_CARD_ROWS.ACCOUNT_ID            
        HAVING SUM(ROUND(ACCOUNT_CARD_ROWS.AMOUNT,2))<>0 
        UNION ALL
            SELECT DISTINCT
                0 AS ALACAK,
                0 AS BORC,
                ACCOUNT_PLAN.ACCOUNT_CODE
            FROM
                ACCOUNT_PLAN,
                ACCOUNT_PLAN ACCOUNT_ACCOUNT_REMAINDER
            WHERE
                ACCOUNT_PLAN.ACCOUNT_CODE NOT IN 
                                    (SELECT 
                                        ACCOUNT_ID 
                                    FROM 
                                        ACCOUNT_CARD_ROWS,
                                        ACCOUNT_CARD
                                    WHERE
                                        ACCOUNT_PLAN.ACCOUNT_CODE = ACCOUNT_CARD_ROWS.ACCOUNT_ID AND
                                        ACCOUNT_CARD_ROWS.CARD_ID = ACCOUNT_CARD.CARD_ID
                                        AND ACCOUNT_CARD.ACTION_DATE BETWEEN  '2013-11-01 00:00:00' AND  '2013-11-30 00:00:00' )

                AND ACCOUNT_PLAN.ACCOUNT_CODE = LEFT(ACCOUNT_ACCOUNT_REMAINDER.ACCOUNT_CODE,3)

        )
        AS ACCOUNT_ACCOUNT_REMAINDER,
        ACCOUNT_PLAN
    WHERE
        1=1
        AND  ACCOUNT_PLAN.ACCOUNT_CODE = LEFT(ACCOUNT_ACCOUNT_REMAINDER.ACCOUNT_ID,3)
    GROUP BY    
        ACCOUNT_PLAN.ACCOUNT_CODE, 
        ACCOUNT_PLAN.ACCOUNT_NAME,
    ACCOUNT_PLAN.ACCOUNT_ID,
    ACCOUNT_PLAN.SUB_ACCOUNT    
    )T1
    GROUP BY
        ACCOUNT_CODE, 
        ACCOUNT_NAME,
        ACCOUNT_ID,
        SUB_ACCOUNT     
    ORDER BY    
        ACCOUNT_CODE
</cfquery>

This query is so slow. For this I try use query of queries. I coded something.

<cfquery name="ACCOUNT_PLAN" datasource="#dsn2#">
select * from ACCOUNT_PLAN
</cfquery>
<cfquery name="ACCOUNT_CARD" datasource="#dsn2#">
select * from ACCOUNT_CARD
</cfquery>
<cfquery name="ACCOUNT_CARD_ROWS" datasource="#dsn2#">
select * from ACCOUNT_CARD_ROWS
</cfquery>
<cfquery name="GET_ACC_REMAINDER" dbtype="query">
    SELECT
        SUM(BAKIYE) AS BAKIYE, 
        SUM(BORC) AS BORC,
        SUM(ALACAK) AS ALACAK, 
        ACCOUNT_CODE,
        ACCOUNT_NAME,
        ACCOUNT_ID
    FROM
    (
    SELECT
        ROUND(SUM(ACCOUNT_ACCOUNT_REMAINDER.BORC - ACCOUNT_ACCOUNT_REMAINDER.ALACAK),2) AS BAKIYE, 
        ROUND(SUM(ACCOUNT_ACCOUNT_REMAINDER.BORC),2) AS BORC,
        ROUND(SUM(ACCOUNT_ACCOUNT_REMAINDER.ALACAK),2) AS ALACAK, 
        ACCOUNT_PLAN.ACCOUNT_CODE,
        ACCOUNT_PLAN.ACCOUNT_NAME,
    ACCOUNT_PLAN.ACCOUNT_ID,
    ACCOUNT_PLAN.SUB_ACCOUNT
    FROM
        (
        SELECT
            0 AS ALACAK,
             SUM(ROUND(ACCOUNT_CARD_ROWS.AMOUNT,2)) AS BORC,
            ACCOUNT_CARD_ROWS.ACCOUNT_ID    
        FROM
            ACCOUNT_CARD_ROWS,ACCOUNT_CARD
        WHERE
            BA = 0 AND ACCOUNT_CARD.CARD_ID=ACCOUNT_CARD_ROWS.CARD_ID
            AND ACTION_DATE BETWEEN  '2013-11-01 00:00:00' AND  '2013-11-30 00:00:00' GROUP BY
            ACCOUNT_CARD_ROWS.ACCOUNT_ID
        HAVING SUM(ROUND(ACCOUNT_CARD_ROWS.AMOUNT,2))<>0
    UNION ALL
        SELECT
             SUM(ROUND(ACCOUNT_CARD_ROWS.AMOUNT,2)) AS ALACAK,
            0 AS BORC,
            ACCOUNT_CARD_ROWS.ACCOUNT_ID        
        FROM
            ACCOUNT_CARD_ROWS,
            ACCOUNT_CARD
        WHERE
            BA = 1 AND ACCOUNT_CARD.CARD_ID=ACCOUNT_CARD_ROWS.CARD_ID       
            AND ACTION_DATE BETWEEN  '2013-11-01 00:00:00' AND  '2013-11-30 00:00:00' GROUP BY
            ACCOUNT_CARD_ROWS.ACCOUNT_ID            
        HAVING SUM(ROUND(ACCOUNT_CARD_ROWS.AMOUNT,2))<>0 
        UNION ALL
            SELECT DISTINCT
                0 AS ALACAK,
                0 AS BORC,
                ACCOUNT_PLAN.ACCOUNT_CODE
            FROM
                ACCOUNT_PLAN,
                ACCOUNT_PLAN ACCOUNT_ACCOUNT_REMAINDER
            WHERE
                ACCOUNT_PLAN.ACCOUNT_CODE NOT IN 
                                    (SELECT 
                                        ACCOUNT_ID 
                                    FROM 
                                        ACCOUNT_CARD_ROWS,
                                        ACCOUNT_CARD
                                    WHERE
                                        ACCOUNT_PLAN.ACCOUNT_CODE = ACCOUNT_CARD_ROWS.ACCOUNT_ID AND
                                        ACCOUNT_CARD_ROWS.CARD_ID = ACCOUNT_CARD.CARD_ID
                                        AND ACCOUNT_CARD.ACTION_DATE BETWEEN  '2013-11-01 00:00:00' AND  '2013-11-30 00:00:00' )

                AND ACCOUNT_PLAN.ACCOUNT_CODE = LEFT(ACCOUNT_ACCOUNT_REMAINDER.ACCOUNT_CODE,3)

        )
        AS ACCOUNT_ACCOUNT_REMAINDER,
        ACCOUNT_PLAN
    WHERE
        1=1
        AND  ACCOUNT_PLAN.ACCOUNT_CODE = LEFT(ACCOUNT_ACCOUNT_REMAINDER.ACCOUNT_ID,3)
    GROUP BY    
        ACCOUNT_PLAN.ACCOUNT_CODE, 
        ACCOUNT_PLAN.ACCOUNT_NAME,
    ACCOUNT_PLAN.ACCOUNT_ID,
    ACCOUNT_PLAN.SUB_ACCOUNT    
    )T1
    GROUP BY
        ACCOUNT_CODE, 
        ACCOUNT_NAME,
        ACCOUNT_ID,
        SUB_ACCOUNT     
    ORDER BY    
        ACCOUNT_CODE
</cfquery>

Coldfusion debug showing this error.

Error Executing Database Query. Query Of Queries syntax error. Encountered "(. The error occurred on line 10.

Have you any idea ?

Answer 1

Stormcloak, I see what you are trying to do, but your first effort should be to try and determine why the original query is slow. If you wanted to do this via Q of a Q you could not (as Dan has said) use subqueries. Q of a Q supports a fairly discreet subset of commands and syntax. Selecting FROM a subquery of q of a q would not be supported.

However, if you wanted to take advantage of Q of a Q and were willing to move the subquery logic to code you could do something like this psuedo code:

<cfquery name="ACCOUNT_PLAN" datasource="#dsn2#">
select * from ACCOUNT_PLAN
</cfquery>
<cfquery name="ACCOUNT_CARD" datasource="#dsn2#">
select * from ACCOUNT_CARD
</cfquery>
<cfquery name="ACCOUNT_CARD_ROWS" datasource="#dsn2#">
select * from ACCOUNT_CARD_ROWS
</cfquery>

<!---MAK:   get the unique account_ids--->
<cfquery name="getIDs" dbtype="query">
    SELECT UNIQUE ACCOUNT_ID
    FROM    ACCOUNT_PLAN  
</cfquery>


<Cfset GET_ACC_REMAINDER = querynew("BAKIYE,BORC,ALACAK,ACCOUNT_CODE,ACCOUNT_NAME,ACCOUNT_ID","decimal,decimal,decimal,varchar,varchar,varchar")/>

<cfloop query="getIDs">
    <!---MAK:   Pull out a subset of data based on account_ID--->
    <cfquery name="getPlans" dbtype="query">
        SELECT * FROM ACCOUNT_PLAN  
        WHERE    account_id = '#account_id#'    
    </cfquery>
    <cfquery name="getCards" dbtype="query">
        SELECT * FROM ACCOUNT_CARD  
        WHERE    account_id = '#account_id#'    
    </cfquery>
    <cfquery name="getPlans" dbtype="query">
        SELECT * FROM ACCOUNT_CARD_ROWS  
        WHERE    account_id = '#account_id#'    
    </cfquery>

    Now, using implement your logic using these subqueries - probably with a block of code to calculate BAKIYE, BORC, AND ALACAK - 

    <cfset BAKIYE = 0/>
    <!---MAK:   Block 1 - calculate BAKIYE--->  
    ... code for BAKIYE goes here

    <cfset BORC = 0/>
    <!---MAK:   block 2 calculate BORC--->
    ... code for BORC...

    <cfset ALACAK = 0/>
    <!---MAK:   block 3 calclate ALACAK--->
    ... code for ALACAK 

    Final step:

    <cfset querysetcell(GET_ACC_REMAINDER,"BAKIYE",BAKIYE)/>
    <cfset querysetcell(GET_ACC_REMAINDER,"BORC",BORC)/>
    <cfset querysetcell(GET_ACC_REMAINDER,"ALACAK",ALACAK)/>
    <cfset querysetcell(GET_ACC_REMAINDER,"ACCOUNT_CODE",getPlans.ACCOUNT_CODE)/>
    <cfset querysetcell(GET_ACC_REMAINDER,"ACCOUNT_NAME",getplans.ACCOUNT_NAME)/>
    <cfset querysetcell(GET_ACC_REMAINDER,"ACCOUNT_ID",ACCOUNT_ID)/>

</cfloop>

The end result is a query, GET_ACC_REMAINDER populated with values like the one from the complex union/subquery.

This would involve reengineering the logic found in the complex union query - which is out of scope for a stack overflow solution - but you get the idea. Given the complex nature of your query I think this is a hefty undertaking and I would make sure and QA the heck out it. Not only will the logic be hairy, but the way the DB treats a number for rounding etc. might vary from how Java treats it - so that's another gotcha to consider.

Note, there may be tables you need to get in your original queries too - I noticed an ACCOUNT_ACCOUNT_REMAINDER for example that I can't seem to identify as an aggregate - so maybe it's a table. You will doubtless need additional q of a q, math operations and IF elses inside each of your blocks for calculating.

Now the question may be should you do this (which I have sort of answered with all my Chicken Little caution)? I would probably be in Adam's camp and advise against it. From a performance perspective it will probably not save you much - especially if you have to hit the database for additional data within your logic blocks. In the end it could conceivably be slower. It is logical that this sort of thing live on the DB Server which is equipped for this sort of complex logic. I would probably spend my time tuning the query on the server or rewriting it as a stored proc with a temp table perhaps.

Q of a Q is a great tool, but not quite sophisticated enough for how you are trying to use it :)

Answer 2

Query of queries does not support subqueries.