Home » Developer & Programmer » Reports & Discoverer » display multiple rows (oracle reports 8i)
display multiple rows [message #346611] Tue, 09 September 2008 02:27 Go to next message
myura
Messages: 66
Registered: July 2007
Location: Malaysia
Member

hi all..good day

i want to display multiple rows from this table. fyi, im using oracle reports 8i. at data model section, i hv 3 groups, A,B and C. at group C, i add a formula column which is contain pl/sql statement (a function i create to select and return multiple rows). at layout section, the column which is refer to that formula column already put in a repeating frame which is reset at group C. but somehow, when i run the report, the column doesn't appear anything. i know there's something missing at my layout..anyone can help me?
Re: display multiple rows [message #346638 is a reply to message #346611] Tue, 09 September 2008 03:59 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Perhaps the function doesn't return anything.
Re: display multiple rows [message #346645 is a reply to message #346611] Tue, 09 September 2008 04:08 Go to previous messageGo to next message
myura
Messages: 66
Registered: July 2007
Location: Malaysia
Member

i've query in toad n the values come out. The same thing should come out the report. but i got none. maybe my statement is wrong? below is my statement:

FUNCTION exchangerate RETURN number IS
exchange_rate number;
BEGIN
Select rate into exchange_rate
from re06rttyp
where yr=:yr
and seqno=:seqno
and seqcnt=:seqcnt
and brncd=:brncd;
Exception when no_data_found then
exchange_rate := :fcrate;

return exchange_rate;

END;
Re: display multiple rows [message #346652 is a reply to message #346645] Tue, 09 September 2008 04:27 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
There's no RETURN statement in a function body; the only case in which "something" gets returned is the NO-DATA-FOUND exception. Perhaps you should rewrite it as
FUNCTION exchangerate RETURN number IS
  exchange_rate number;
BEGIN
  Select rate into exchange_rate 
  from re06rttyp 
  where yr=:yr 
   and seqno=:seqno
   and seqcnt=:seqcnt 
   and brncd=:brncd;

  RETURN exchange_rate;    --> you miss this line!

Exception 
  when no_data_found then
    exchange_rate := :fcrate;
    return exchange_rate;

END;
Re: display multiple rows [message #346660 is a reply to message #346611] Tue, 09 September 2008 04:49 Go to previous messageGo to next message
myura
Messages: 66
Registered: July 2007
Location: Malaysia
Member

i already put that line, but still it goes to the exception block..seems like it skips the begin block.
Re: display multiple rows [message #347156 is a reply to message #346660] Wed, 10 September 2008 21:58 Go to previous messageGo to next message
myura
Messages: 66
Registered: July 2007
Location: Malaysia
Member

i turn my code into this:


function exchangerate return NUMBER is

cursor c_rate
is
Select rate
from re06rttyp
where yr=:yr
and seqno=:seqno
and seqcnt=:seqcnt
and brncd=:brncd
and rate is not null;

exchange_rate number;

begin
for i in c_rate
loop
exchange_rate := i.rate;
end loop;

return exchange_rate;

Exception
when no_data_found then
exchange_rate := :fcrate; /*fcrate is a column name from another table*/

return exchange_rate;

end;


i tried this code, but also disappointed me. Actually the problem is like this. Firstly i want to display values at column rate from table re06rttyp ( which will return multiple rows). And if no data found in this table, it will take value at column fcrate (from another table) which will return single row.

When i run this function at my report, it will show value from rate column as 0 (because i set up at my report if value is null, then display 0). Seems it doesn't goes to the exception part.
Re: display multiple rows [message #347217 is a reply to message #346611] Thu, 11 September 2008 01:51 Go to previous messageGo to next message
myura
Messages: 66
Registered: July 2007
Location: Malaysia
Member

here is my latest code. i got the values but when try to change to numeric, it gives me error numeric during runtime report. got any idea? maybe i miss out smthing here..hmm..

function exchangerate return number is

cursor c_rate
is
Select rate
from re06rttyp
where yr=:yr
and seqno=:seqno
and brncd=:brncd
and rate is not null;

exchange_rate number;
xrate varchar2(100);

Begin
for i in c_rate
loop
xrate := xrate||chr(9)||i.rate;
end loop;

If xrate is null then
xrate := :fcrate;

exchange_rate := to_number(xrate);
--dbms_output.put_line (exchange_rate );
return exchange_rate;

end if;

exchange_rate := to_number(xrate);
return exchange_rate;

end;
Re: display multiple rows [message #347223 is a reply to message #347217] Thu, 11 September 2008 02:10 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
It seems that TO_NUMBER can't do the job against xrate || chr(9) || i.rate.
Re: display multiple rows [message #347274 is a reply to message #346611] Thu, 11 September 2008 04:16 Go to previous message
myura
Messages: 66
Registered: July 2007
Location: Malaysia
Member

sigh..then, how do i change it to numeric...? Confused
Previous Topic: how to design vertical line in rdf
Next Topic: Setting a title to the PDF doc.
Goto Forum:
  


Current Time: Sat Dec 10 14:32:11 CST 2016

Total time taken to generate the page: 0.04649 seconds