Home » Developer & Programmer » Reports & Discoverer » oracle report problem
oracle report problem [message #153977] Thu, 05 January 2006 03:54 Go to next message
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 #153997 is a reply to message #153977] Thu, 05 January 2006 05:37 Go to previous messageGo to next message
Littlefoot
Messages: 21821
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
:CP_1 := NVL(v_reload_amount_ain - v_reload_amount_ppb, 0);

might solve the problem.
Re: oracle report problem [message #155323 is a reply to message #153977] Fri, 13 January 2006 17:59 Go to previous message
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)
Previous Topic: between pages
Next Topic: help needed with SQL query...discoverer report
Goto Forum:
  


Current Time: Sat Nov 09 19:32:49 CST 2024