Home » Applications » Oracle Fusion Apps & E-Business Suite » NVL
NVL [message #406779] |
Fri, 05 June 2009 16:15 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
Anne Simms
Messages: 163 Registered: May 2002 Location: San Francisco, CA
|
Senior Member |
|
|
When I run the following query:
SELECT gi.award_id award_id, pfs.project_id project_id, nvl(pfs.task_id,0)task_id,
SUM (NVL (pfs.total_funding_amount, 0)) funding
FROM apps.gms_summary_project_fundings pfs JOIN apps.gms_installments gi
ON gi.installment_id = pfs.installment_id
WHERE pfs.installment_id IN (
SELECT MAX (g.installment_id)
FROM apps.gms_installments g JOIN apps.gms_awards_all ga
ON ga.award_id = g.award_id
WHERE g.installment_id =
pfs.installment_id)
and gi.award_id IN (15037,4531)
and pfs.project_id IN (23043,8749)
GROUP BY gi.award_id, pfs.project_id, pfs.task_id
The output is as follows:
AWARD_ID PROJECT_ID TASK_ID FUNDING
4,531.00 8,749.00 8,661.00 236,765.68
4,531.00 8,749.00 8,662.00 41,259.04
4,531.00 8,749.00 8,663.00 52,388.73
15,037.00 23,043.00 0 275,256.00
The problem is in my report the funding column for the dollar amount $275,256.00 will not appear in my report it will just appear as null. All other funding amounts appear in the report, except the $275,256.00. Also pfs.task_id column description in pfs table can be null.
So how can I get the $275,256.00 funding amount to appear in my report?
Anne
|
|
|
|
|
|
Re: NVL [message #407959 is a reply to message #407616] |
Fri, 12 June 2009 10:40 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) |
Anne Simms
Messages: 163 Registered: May 2002 Location: San Francisco, CA
|
Senior Member |
|
|
Here is the query again, this time I added NVL to the sum amount.
SELECT gi.award_id award_id, pfs.project_id project_id,
NVL (pfs.task_id, 0) task_id,
SUM (NVL (pfs.total_funding_amount, 0)) funding
FROM apps.gms_summary_project_fundings pfs JOIN apps.gms_installments gi
ON gi.installment_id = pfs.installment_id
WHERE pfs.installment_id IN (SELECT MAX (g.installment_id)
FROM apps.gms_installments g JOIN apps.gms_awards_all ga
ON ga.award_id = g.award_id
WHERE g.installment_id = pfs.installment_id)
pfs.task_id is null
GROUP BY gi.award_id, pfs.project_id, pfs.task_id
Output:
AWARD_ID,PROJECT_ID,TASK_ID,FUNDING
1218,38153,0,0
9813,16195,0,0
9813,16196,0,0
9813,16197,0,0
10433,13751,0,0
[B]15037,23043,0,275256[/B]
190713,50627,0,0
The funding amount of 275256 appears when I run the query by itself.
This is how it appears in the report:
The report is running from our sub directories in which reside the concurrent programs and reports. $SAM_TOP/sql - for customized reports written in sql plus.
Anne
|
|
|
Goto Forum:
Current Time: Wed Aug 28 09:36:28 CDT 2024
|