Two extra columns with using HAVING in QoQ Coldfusion

Question

When I use HAVING in my QoQ coldfusion, the query returned will have two extra column: "Column_7" and "Column_8"

The original resultat

And the resultat with two extra columns

Here is my code

var qEffectifTemp = queryExecute("
 SELECT CONVIVETYPELABEL, 
    SUM(EFFECTIFITEMVALEURPREVISIONNELLE) AS REPASPREVISIONNELLETOTAL, 
    SUM(EFFECTIFITEMVALEURSAISIE) AS REPASSAISIETOTAL, 
    OFFICENAME, 
    SATELLITENAME,
    REPASTYPELABEL,
    CUISINECENTRALENAME
 FROM   qEffectifsItemTemp
 GROUP BY REPASTYPELABEL, SATELLITENAME, CONVIVETYPELABEL, OFFICENAME, CUISINECENTRALENAME
 HAVING SUM(EFFECTIFITEMVALEURPREVISIONNELLE) <> SUM(EFFECTIFITEMVALEURSAISIE)", {}, {dbtype="query"}
);

So why? Thank you for your helps

Answer 1

The problem is you are not using the aliases properly.

This sample query produces the same issue:

<cfquery  name="childQuery" dbtype="query">
    SELECT sum(age) as Total, lastname FROM parentQuery
    GROUP BY id,lastname
    HAVING sum(age) > 10
</cfquery>

Using the aliases in the HAVING clause, like in the below query, resolves the issue:

 <cfquery  name="childQuery" dbtype="query">
        SELECT sum(age) as Total, lastname FROM parentQuery
        GROUP BY id,lastname
        HAVING Total > 10
    </cfquery>

Your problem is you already created aliases for the columns in the sql:

, SUM(EFFECTIFITEMVALEURPREVISIONNELLE) AS REPASPREVISIONNELLETOTAL 
, SUM(EFFECTIFITEMVALEURSAISIE) AS REPASSAISIETOTAL

Using the SUM's again in the HAVING clause creates extra columns like column_7 & column_8. Instead, you should use the aliases:

HAVING REPASPREVISIONNELLETOTAL <> REPASSAISIETOTAL

So your full query should look like the one below:

var qEffectifTemp = queryExecute("
         SELECT CONVIVETYPELABEL, 
            SUM(EFFECTIFITEMVALEURPREVISIONNELLE) AS REPASPREVISIONNELLETOTAL, 
            SUM(EFFECTIFITEMVALEURSAISIE) AS REPASSAISIETOTAL, 
            OFFICENAME, 
            SATELLITENAME,
            REPASTYPELABEL,
            CUISINECENTRALENAME
         FROM   qEffectifsItemTemp
         GROUP BY REPASTYPELABEL, SATELLITENAME, CONVIVETYPELABEL, OFFICENAME, CUISINECENTRALENAME
         HAVING REPASPREVISIONNELLETOTAL <> REPASSAISIETOTAL", {}, {dbtype="query"}
        );