OraFAQ Forum: Reports & Discoverer » display multiple rows

Home » Developer & Programmer » Reports & Discoverer » display multiple rows (oracle reports 8i)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

display multiple rows [message #346611]

Tue, 09 September 2008 02:27

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?

Report message to a moderator

Re: display multiple rows [message #346638 is a reply to message #346611]

Tue, 09 September 2008 03:59

Littlefoot
Messages: 21808
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

Perhaps the function doesn't return anything.

Report message to a moderator

Re: display multiple rows [message #346645 is a reply to message #346611]

Tue, 09 September 2008 04:08

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;

Report message to a moderator

Re: display multiple rows [message #346652 is a reply to message #346645]

Tue, 09 September 2008 04:27

Littlefoot
Messages: 21808
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;

Report message to a moderator

Re: display multiple rows [message #346660 is a reply to message #346611]

Tue, 09 September 2008 04:49

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.

Report message to a moderator

Re: display multiple rows [message #347156 is a reply to message #346660]

Wed, 10 September 2008 21:58

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.

Report message to a moderator

Re: display multiple rows [message #347217 is a reply to message #346611]

Thu, 11 September 2008 01:51

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;

Report message to a moderator