When I use HAVING in my QoQ coldfusion, the query returned will have two extra column: "Column_7" and "Column_8"
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
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"}
);