CODEWITHSUNDEEP
sqlsql-serverdatabase
lytakyn
lytakyn

Reputation: 62

Query returning null values but there is no error

I am picking values from a column TRANSAMT from the table GLPJD. The value can be negative in the table but when it's negative, I have to make it positive and when its positive, I leave it like that. The result being returned in NULL, what is left in the query. It is a subquery

declare @FY int
set @FY = 2016

SELECT
    DbName, District,
    (SELECT 
         CASE   
            WHEN (TRANSAMT < 0) 
               THEN ISNULL(SUM(TRANSAMT) * -1, 0) 
               ELSE ISNULL(SUM(TRANSAMT), 0)  
         END  
     FROM 
         GLPJD 
     LEFT JOIN 
         GLAMF ON GLPJD.ACCTID = GLAMF.ACCTID  
     WHERE 
         GLPJD.AUDTORG = D.DbName 
         AND GLPJD.ACCTID = 110002 
         AND GLPJD.FISCALYR < @FY 
     GROUP BY
         TRANSAMT) AS TRANSAMT
FROM
    Districts D

Results of the query:

results of the query

Upvotes: 1

Views: 1677

Answers (2)

Pioter
Pioter

Reputation: 13

sum function omits null values look: http://sqlfiddle.com/#!4/0a806/1

Upvotes: 0

Jorge Y.
Jorge Y.

Reputation: 1133

Your error comes probably from the fact that performing a SUM while having NULL values returns a NULL result. So you should use the ISNULL part inside SUM, and not the other way around.

Also you may consider using the function ABS, so you could get rid of the CASE part. My approach would be changing this part from your current code:

SELECT 
         CASE   
            WHEN (TRANSAMT < 0) 
               THEN ISNULL(SUM(TRANSAMT) * -1, 0) 
               ELSE ISNULL(SUM(TRANSAMT), 0)  
         END

To just:

SELECT SUM(ABS(ISNULL(TRANSAMT, 0)))

Upvotes: 1

Related Questions