SQL issue with NULL values on SUM

Question

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  

Answer 1

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.

Answer 2

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)))