CODEWITHSUNDEEP
sql-serversql-server-2005coldfusionhaving-clause
tsurahman
tsurahman

Reputation: 1912

ColdFusion MsSQL HAVING SUM() with NULL value

I tested this with CF 9 and MsSQL 2005

CREATE TABLE HAVING_SUM
(
    A_VARCHAR VARCHAR(5),
    B_INT INT
)

INSERT INTO HAVING_SUM ( A_VARCHAR, B_INT ) VALUES ( 'AB', 2 )
INSERT INTO HAVING_SUM ( A_VARCHAR, B_INT ) VALUES ( 'AB', 3 )
INSERT INTO HAVING_SUM ( A_VARCHAR, B_INT ) VALUES ( 'AB', 5 )
INSERT INTO HAVING_SUM ( A_VARCHAR, B_INT ) VALUES ( 'CD', 2 )
INSERT INTO HAVING_SUM ( A_VARCHAR, B_INT ) VALUES ( 'CD', 7 )
INSERT INTO HAVING_SUM ( A_VARCHAR, B_INT ) VALUES ( 'CD', 8 )
INSERT INTO HAVING_SUM ( A_VARCHAR, B_INT ) VALUES ( 'CD', NULL)

and then query in Ms SQL Server Management Studio Express

SELECT  *
FROM    HAVING_SUM

SELECT  A_VARCHAR,
        SUM(B_INT) AS B_INT
FROM    HAVING_SUM
GROUP BY A_VARCHAR

SELECT  A_VARCHAR,
        SUM(B_INT) AS B_INT
FROM    HAVING_SUM
GROUP BY A_VARCHAR
HAVING SUM(B_INT) = 10

SELECT  A_VARCHAR,
        SUM(B_INT) AS B_INT
FROM    HAVING_SUM
GROUP BY A_VARCHAR
HAVING SUM(B_INT) = 10.5

the result is

alt text

then I tried using cfquery

<cfquery name="qryHavingSum_1">
SELECT  *
FROM    HAVING_SUM
</cfquery>

<cfdump var="#qryHavingSum_1#">

<cfquery name="qryHavingSum_2">
SELECT  A_VARCHAR,
        SUM(B_INT) AS B_INT
FROM    HAVING_SUM
GROUP BY A_VARCHAR
</cfquery>

<cfdump var="#qryHavingSum_2#">

<cfquery name="qryHavingSum_3">
SELECT  A_VARCHAR,
        SUM(B_INT) AS B_INT
FROM    HAVING_SUM
GROUP BY A_VARCHAR
HAVING SUM(B_INT) = 10
</cfquery>

<cfdump var="#qryHavingSum_3#">

<cfquery name="qryHavingSum_4">
SELECT  A_VARCHAR,
        SUM(B_INT) AS B_INT
FROM    HAVING_SUM
GROUP BY A_VARCHAR
HAVING SUM(B_INT) = 10.5
</cfquery>

<cfdump var="#qryHavingSum_4#">

and the result is

alt text alt text

Edit (start here): scroll down to debugger I've got this

alt text

that red area indicated that qryHavingSum_4 is executed, with 0 record, but in the Exception section qryHavingSum_4 is undefined (the <cfdump var="#qryHavingSum_4#">) Edit (end here)

if I change qryHavingSum_4 to

<cfquery name="qryHavingSum_4">
SELECT  A_VARCHAR,
        SUM(ISNULL(B_INT, 0)) AS B_INT
FROM    HAVING_SUM
GROUP BY A_VARCHAR
HAVING SUM(ISNULL(B_INT, 0)) = 10.5
</cfquery>

the result is

alt text

and then I tried to change SUM(B_INT) (without ISNULL) and HAVING SUM(B_INT) (without ISNULL) with other operator > >= < <= <> != and it works.

why I can't use the = with query above in CF? is it CFbugs?

thank you

Upvotes: 0

Views: 1378

Answers (1)

KobbyPemson
KobbyPemson

Reputation: 2539

Management studio strips out the nulls (when you run it check the messages tab and you'll see the following )

Warning: Null value is eliminated by an aggregate or other SET operation.

The coldfusion mssql driver does not.

Using the isnull function is the best approach.

Alternatively you can disable ansi warnings in your cfquery and re-enable them after they run So you can change query 4 to 

SET ANSI_WARNINGS OFF
SELECT  A_VARCHAR,
        SUM(B_INT) AS B_INT
FROM    HAVING_SUM
GROUP BY A_VARCHAR
HAVING SUM(B_INT)=10.5
SET ANSI_WARNINGS ON

Upvotes: 4

Related Questions