How do I change a SQL query so that the SUM function returns a 0 when there is no data found?

Assuming Oracle .... if you mean the overall SUMs are returning NULL, wrap them in either NVL() or COALESCE()

SELECT
  NVL(
         SUM(   CASE
                         WHEN P.ACCOUNTING_DATE BETWEEN i.terms_date AND i.terms_date + 15 THEN i.invoice_amount
                         ELSE 0    
                      END
        )
  , 0)  "less15days"

,   repeat for other columns

Please bear with me. I'm not a sql coder. I've added the code above to the query:

SELECT
   /*+ INDEX (f FV_INV_SELECTED_DUEDATE_IDX) */
   --Within 15 days
  NVL(	
   SUM(CASE
      WHEN
         P.ACCOUNTING_DATE BETWEEN i.terms_date AND i.terms_date + 15 
      THEN
         i.invoice_amount 
      ELSE
         0 
   END
,0) "less15days", 
	  NVL(
	SUM(CASE
      WHEN
         P.ACCOUNTING_DATE BETWEEN i.terms_date AND i.terms_date + 15 
      THEN
         1 
      ELSE
         0 
   END
,0) "15COUNT", 
--Between 15-30 Days
NVL(
	SUM(
   CASE
      WHEN
         P.ACCOUNTING_DATE BETWEEN i.terms_date + 16 AND i.terms_date + 30 
      THEN
         i.invoice_amount 
      ELSE
         0 
   END
,0) "15to30days", SUM(
   CASE
      WHEN
         P.ACCOUNTING_DATE BETWEEN i.terms_date + 16 AND i.terms_date + 30 
      THEN
         1 
      ELSE
         0 
   END
) "1530C", 
--After 30 Days
NVL(SUM(
   CASE
      WHEN
         P.ACCOUNTING_DATE > i.terms_date + 30 
      THEN
         i.invoice_amount 
      ELSE
         0 
   END
,0) "30plusdays", 
NVL(
	SUM(
   CASE
      WHEN
         P.accounting_date > i.terms_date + 30 
      THEN
         1 
      ELSE
         0 
   END
,0) "30plusC" 
FROM
   ap.ap_terms_tl t , ap.ap_invoice_payments_all p , ap.ap_invoices_all i , ap.ap_payment_schedules_all s 
WHERE
   t.NAME <> 'PROMPT SB NET 30' 	--take out small business terms
   --and t.NAME not like 'STANDARD%'  -- take out standard terms
   --and nvl(P.DISCOUNT_TAKEN,0) =0    --take out discounts paid
   AND I.PAY_GROUP_LOOKUP_CODE IN
   (
      'VENDOR', 'VENDOR No-Bud', 'NONGOV-PAY-OTHER-BUD', 'NONGOV-PAY-ADV-NO BUD', 'NONGOV-PAY SUS NO BUD'
   )
   AND T.TERM_ID = I.TERMS_ID 
   AND p.invoice_id = i.invoice_id 
   AND p.invoice_id = s.invoice_id 
   AND p.org_id = '#form.agency#' 
   AND p.accounting_date BETWEEN (
   SELECT
      start_date 
   FROM
      gl.gl_periods a, gl.gl_sets_of_books b, fv.fv_operating_units_all c 
   WHERE
      a.period_name = UPPER('#form.period#') AND a.period_set_name = b.period_set_name 
      AND b.set_of_books_id = c.set_of_books_id 
      AND c.organization_id = '#form.agency#' ) 
      AND 
      (
         SELECT
            end_date 
         FROM
            gl.gl_periods a,
            gl.gl_sets_of_books b,
            fv.fv_operating_units_all c 
         WHERE
            a.period_name = UPPER('#form.period#') 
            AND a.period_set_name = b.period_set_name 
            AND b.set_of_books_id = c.set_of_books_id 
            AND c.organization_id = '#form.agency#' 
      )
      AND I.invoice_type_lookup_code IN
      (
         'STANDARD',
         'MIXED'
      )
      AND P.REVERSAL_FLAG IS NULL 
      AND i.invoice_amount > 0

Select allOpen in new window

That's throwing an error:

The error occurred in D:/inetpub/wwwroot/CASAPay/casapay.cfc: line 128
Called from D:/inetpub/wwwroot/CASAPay/casapay_out.cfm: line 69
Called from D:/inetpub/wwwroot/CASAPay/casapay.cfc: line 128
Called from D:/inetpub/wwwroot/CASAPay/casapay_out.cfm: line 69
 
126 :             AND a.period_set_name = b.period_set_name 
127 :             AND b.set_of_books_id = c.set_of_books_id 
128 :             AND c.organization_id = '#form.agency#' 
129 :       )
130 :       AND I.invoice_type_lookup_code IN

Select allOpen in new window

Is the code correct above?

>>Is the code correct above?

What database are you using?

it's an Oracle db

You are missing the closing PARAN on the SUM.

        0
   END
)     --<< THIS ONE
,0) "less15days",


That is only one, you've missed several.

Take the select you had and "wrap" the sum with the NVL function.

So if you had sum(bob), you end up with nvl(sum(bob),0)

You might think about downloading sqlplus, sqlcl or SQL Developer to connect directly to the database and test the SQL before trying to just run it in code.

Wrapping the SUM (or COALESCE) with a call to NVL won't help if there is no data found (as suggested by the original post).  If the query truly returns no records, there is nothing for the NVL to work on.  NVL won't create data where there is none.

what will work johnsone if that won't?

It appears as though this is designed to return 1 row.  Are you getting back the 1 row with NULLs in some of the columns, or are you getting back 0 rows.

That determines how  you proceed.

I ran in TOAD and got an invalid number error on the bolded line below with the unchanged query:

(select end_date from gl.gl_periods a,gl.gl_sets_of_books b,fv.fv_operating_units_all c
 where a.period_name=upper('#form.period#')
and a.period_set_name=b.period_set_name
and b.set_of_books_id =c.set_of_books_id
and c.organization_id='#form.agency#'

This would need to be replaced by a valid number: '#form.agency#'

If you change that to: &form_agency  (with no quotes) it should prompt you to provide a valid number.

>> where a.period_name=upper('#form.period#')

Also, replace #form.period# with &form_period  OR just hard code a valid value for testing.

          where a.period_name=upper('SOME_VALID_PERIOD_HERE')

>>Are you getting back the 1 row with NULLs in some of the columns, or are you getting back 0 rows.
>> That determines how  you proceed.

If you're getting back 0 rows, handle it in the application layer using the CF function VAL() as suggested on your other thread.

NVL should do the trick fine.
You'll always get a row back from a group function (though the result may be null!)
So, if null, use NVL to sort it out.

I just ran a query on a table with a numeric PK... select sum(x) from TableY where x is null. This returns null.
Add the nvl... select nvl(sum(x),0) from TableY where x is null and I get 0.

It'll work if you don't mess up the syntax.

>> You'll always get a row back from a group function (though the result may be null!)

It depends on the specific sql statement.   If the statement only includes aggregates, then yes, it will return 1 record, even if the aggregate result is NULL.  However, if the statement includes aggregate functions AND say a "group by" - if no matching records are found - the query won't return ANY records.

The sample code contains no group by... Nvl should work fine here...bit I take your point.