Quack
asked on
How do I change a SQL query so that the SUM function returns a 0 when there is no data found?
I need to change a SQL query so that the SUM function returns a 0 when there is no data found.
Here's the query:
Here's the query:
SELECT
/*+ INDEX (f FV_INV_SELECTED_DUEDATE_IDX) */
--Within 15 days
SUM(
CASE
WHEN
P.ACCOUNTING_DATE BETWEEN i.terms_date AND i.terms_date + 15
THEN
i.invoice_amount
ELSE
0
END
) "less15days", SUM(
CASE
WHEN
P.ACCOUNTING_DATE BETWEEN i.terms_date AND i.terms_date + 15
THEN
1
ELSE
0
END
) "15COUNT", --Between 15-30 Days
SUM(
CASE
WHEN
P.ACCOUNTING_DATE BETWEEN i.terms_date + 16 AND i.terms_date + 30
THEN
i.invoice_amount
ELSE
0
END
) "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
SUM(
CASE
WHEN
P.ACCOUNTING_DATE > i.terms_date + 30
THEN
i.invoice_amount
ELSE
0
END
) "30plusdays", SUM(
CASE
WHEN
P.accounting_date > i.terms_date + 30
THEN
1
ELSE
0
END
) "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
ASKER
Please bear with me. I'm not a sql coder. I've added the code above to the query:
Is the code correct above?
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
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
Is the code correct above?
>>Is the code correct above?
What database are you using?
What database are you using?
ASKER
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.
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.
ASKER
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.
That determines how you proceed.
ASKER
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_al l 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.a gency#'
(select end_date from gl.gl_periods a,gl.gl_sets_of_books b,fv.fv_operating_units_al
where a.period_name=upper('#form
and a.period_set_name=b.period
and b.set_of_books_id =c.set_of_books_id
and c.organization_id='#form.a
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.
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_PERI OD_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.
Also, replace #form.period# with &form_period OR just hard code a valid value for testing.
where a.period_name=upper('SOME_
>>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!)
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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