oracle report problem [message #153977] |
Thu, 05 January 2006 03:54 |
shatishr
Messages: 52 Registered: September 2005 Location: Shah Alam
|
Member |
|
|
hie all
im having a slight trouble here with my oracle report
PROCEDURE get_gap IS
v_reload_amount_ain number(12);
v_reload_amount_ppb number(12);
v_number_reload_ain number(12);
v_number_reload_ppb number(12);
BEGIN
v_reload_amount_ain := 0;
v_reload_amount_ppb := 0;
v_number_reload_ain := 0;
v_number_reload_ppb := 0;
:CP_1 := 0;
:CP_2 := 0;
:CP_3 := 0;
:CP_4 := 0;
select sum(reload_amount), count(reload_amount)
into v_reload_amount_ain, v_number_reload_ain
from trap_ra_k2_ain_balance
where week_no = :p_week;
select sum(reload_amount), count(reload_amount)
into v_reload_amount_ppb, v_number_reload_ppb
from trap_ra_k1_ppb_balance
where week_no = :p_week;
:CP_1 := v_reload_amount_ain - v_reload_amount_ppb;
:CP_2 := v_number_reload_ain - v_number_reload_ppb;
-- checking for 0 divisor
if (v_reload_amount_ppb) > 0 then
:CP_3 := abs(ROUND(:CP_1/(v_reload_amount_ain + v_reload_amount_ppb) *100,2));
else
:CP_3 := 0;
end if;
-- checking for 0 divisor
if (v_number_reload_ppb) > 0 then
:CP_4 := abs(ROUND(:CP_2/(v_number_reload_ain + v_number_reload_ppb) *100,2));
else
:CP_4 := 0;
end if;
END;
the problem here is CP1 is not returning any data in the report it is empty... i tried manually extracting this from db and the sum column is null since there is no data for that particular week for this table
select sum(reload_amount), count(reload_amount)
from trap_ra_k1_ppb_balance
where week_no = 53
what i want is, wherever there is null, it should replace to 0 in
v_reload_amount_ain number(12); and
v_reload_amount_ppb number(12);
any help ?
|
|
|
|
Re: oracle report problem [message #155323 is a reply to message #153977] |
Fri, 13 January 2006 17:59 |
RJ.Zijlstra
Messages: 104 Registered: December 2005 Location: Netherlands - IJmuiden
|
Senior Member |
|
|
Hi, try the following procedure:
PROCEDURE get_gap IS
Cursor C_k1(P_Week pls_integer) is
SELECT sum(reload_amount) theSum,
count(reload_amount) theCount
FROM trap_ra_k2_ain_balance
WHERE week_no = p_week;
R_K1 C_K1%Rowtype;
--
Cursor C_k2(P_Week pls_integer) is
SELECT sum(reload_amount) theSum,
count(reload_amount) theCount
FROM trap_ra_k1_ppb_balance
WHERE week_no = p_week;
R_K2 C_K2%Rowtype;
--
v_reload_amount_ain number(12);
v_reload_amount_ppb number(12);
v_number_reload_ain number(12);
v_number_reload_ppb number(12);
BEGIN
/*
----> totally unnecessary: if no value given in declarartion, a number will default to zero
v_reload_amount_ain := 0;
v_reload_amount_ppb := 0;
v_number_reload_ain := 0;
v_number_reload_ppb := 0;
*/
:CP_1 := 0;
:CP_2 := 0;
:CP_3 := 0; -- 'A'
:CP_4 := 0; -- 'B'
open C_k1( :P-Week)
fetch C_k1 into r_k1;
close c_k1;
v_reload_amount_ain := nvl(R_k1.theSum,0);
v_number_reload_ain := nvl(R_k1.theCount,0);
--
open C_k2( :P-Week)
fetch C_k2 into r_k1;
close c_k2;
v_reload_amount_ppb := nvl( r_k2.theSum);
v_number_reload_ppb := nvl( r_k2.theCount);
--
:CP_1 := v_reload_amount_ain - v_reload_amount_ppb;
:CP_2 := v_number_reload_ain - v_number_reload_ppb;
-- checking for 0 divisor
if (v_reload_amount_ppb) > 0 then------------------------------------------------> this cannot be <0 ?
:CP_3 := abs(ROUND(:CP_1/(v_reload_amount_ain + v_reload_amount_ppb) *100,2));
/*
----> Not nec: you allready did this in 'A'
else
:CP_3 := 0;
*/
end if;
-- checking for 0 divisor
if (v_number_reload_ppb) > 0 then------------------------------------------------> this cannot be <0 ?
:CP_4 := abs(ROUND(:CP_2/(v_number_reload_ain + v_number_reload_ppb) *100,2));
/*
----> Not nec: you allready did this in 'B'
else
:CP_4 := 0;
*/
end if;
END;
Notes:
1) If a number is declared, then it will default to zero.
2) Look at your if-then-else in the last part of the proc. Not nec at all.
3) The MOST important thing:
NEVER, EVRE use a direct select into. If the result is zero or more then one rows, you will get headaches, hearteaches, and more. And it will happen, iven if you are SURE that it will never happen. Take my advice: ALLWAYS use a cursor.
HTH,
(did not test this, beware of typing errors)
|
|
|