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

Will your query execute if you use the alias names from the select clause in the having clause? — Dan Bracuk, Sep 12 '19 at 10:40
Your two unnamed columns are likely because of the two un-aliased `sum()`s in your `HAVING`. Are they causing an issue? Also, is a QoQ necessary here? More often than not, what the QoQ is doing can be done in the original query. — Shawn, Sep 12 '19 at 12:05
What flavor and version of SQL is your base table? And how many rows are in that table? — Shawn, Sep 12 '19 at 12:07
Yes @Shawn. The cause of problem is in aliased only. Please see my answer below . I've explained about root causes of problem. — Kannan.P, Sep 12 '19 at 13:31
@Kannan.P Oddly enough, in most logical query processing engines, a `HAVING` is evaluated BEFORE the `SELECT`, so a `SELECT` aliased column _shouldn't_ be able to be referenced in the `HAVING`. I think MySQL has some odd abilities to reference out of logical cycle, and we _are_ talking about a QoQ here, so pretty much all bets are off. :-/ I still say it may be possible to do what needs to be done in a single query, depending on how else the original query is used. — Shawn, Sep 12 '19 at 14:17
@Shawn. How ever we should give a solution for Column_1 issues. We don't know what his business to handle. May be for their business logic they might wrote a QoQ. So better go with my suggestion to solve the problem for now. Actually it's easy and proper way too. — Kannan.P, Sep 12 '19 at 14:25
I'm usually of the opinion that if a QoQ can be avoided, then it should be. In my experience, QoQ is used, more often than not, to solve a query programmatically rather than in the database where it should be. I don't know this application, so I can't say if a QoQ is necessary here, but I still say that the inconsistent "SQL" of QoQ can lead to some bad habits or confusing results. In most major flavors of SQL (except MySQL/Maria & SQLLite) an alias isn't available in the `HAVING` clause. So that habit could easily lead to questions as to why that same pattern doesn't work in the base query. — Shawn, Sep 12 '19 at 14:49
https://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=ef9bb402d7bd03d9499e4ba23c5e4a23 — Shawn, Sep 12 '19 at 14:51
Agreed. Adobe alludes to the fact that QoQ's use non-standard syntax in the "Using group by and having expressions" section, but ... ugh. Once again, QoQ's doing the unexpected.. — SOS, Sep 12 '19 at 16:24

score 2 · Accepted Answer · edited Sep 13 '19 at 06:48

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"}
        );

Two extra columns with using HAVING in QoQ Coldfusion

1 Answers1