CODEWITHSUNDEEP
sql
Lex
Lex

Reputation: 891

SQL issue with NULL values on SUM

I'm currently working on some sql stuff, but running in a bit of an issue.

I've got this method that looks for cash transactions, and takes off the cashback but sometimes there are no cash transactions, so that value turns into NULL and you can't subtract from NULL. I've tried to put an ISNULL around it, but it still turns into null.

Can anyone help me with this?

;WITH tran_payment AS
(
SELECT 1 AS payment_method, NULL AS payment_amount, null as tran_header_cid
UNION ALL
SELECT 998 AS payment_method, 2 AS payment_amount, NULL as tran_header_cid
), 
paytype AS
(
SELECT 1 AS mopid, 2 AS mopshort
),
tran_header AS
(
SELECT 1 AS cid
)
            SELECT p.mopid                     AS mopid,
                   p.mopshort                  AS descript,
                   payment_value AS PaymentValue,  
                   ISNULL(DeclaredValue, 0.00) AS DeclaredValue
            from   paytype p
                   LEFT OUTER JOIN (SELECT CASE 
                       When (tp.payment_method = 1) 
                       THEN
                     (ISNULL(SUM(tp.payment_amount), 0)
                     - (SELECT ISNULL(SUM(ABS(tp.payment_amount)), 0)
                           FROM tran_payment tp
                           INNER JOIN tran_header th on tp.tran_header_cid = th.cid
        WHERE payment_method = 998
        ) )
     ELSE SUM(tp.payment_amount)
     END as payment_value,
     tp.payment_method,
     0   as DeclaredValue
     FROM   tran_header th
     LEFT OUTER JOIN tran_payment tp
     ON tp.tran_header_cid = th.cid
     GROUP  BY payment_method) pmts
     ON p.mopid = pmts.payment_method

Upvotes: 3

Views: 11272

Answers (3)

Kristof Claes
Kristof Claes

Reputation: 10941

Maybe COALESCE() can help you?

You can try this:

SUM(COALESCE(tp.payment_amount, 0))

or

COALESCE(SUM(tp.payment_amount), 0)

COALESCE(arg1, arg2, ..., argN) returns the first non-null argument from the list.

Upvotes: 5

Daniel Hilgarth
Daniel Hilgarth

Reputation: 174299

I don't have MS SQL to test here, but would it work to put the ISNULL around the SELECT? Maybe, ISNULL isn't triggered at all, if there are no matching rows...

Upvotes: 1

bpgergo
bpgergo

Reputation: 16037

try to put ISNULL inside SUM and ABS, i.e. around the actual field, like this

SUM(ISNULL(tp.payment_amount, 0))

SUM(ABS(ISNULL(tp.payment_amount, 0)))

Upvotes: 2

Related Questions