Home » Developer & Programmer » Reports & Discoverer » creating a formula in oracle reports - error
creating a formula in oracle reports - error [message #236432] Thu, 10 May 2007 00:08 Go to next message
rkhatiwala
Messages: 166
Registered: April 2007
Senior Member
Hi all,

I have created a formula as follows:

function CF_1Formula return CHAR is
MNYR_ST VARCHAR2(15);
MNYR_END VARCHAR2(15);
MNYR VARCHAR2(35);
begin
IF :P_ST_TM_PR1 = :P_END_TM_PR1 THEN
select to_char(to_date(:P_ST_TM_PR1,'yyyymm'),'Month yyyy') into MNYR from dual;
ELSE
select to_char(to_date(:P_ST_TM_PR1, 'yyyymm'), 'Month yyyy' ) into MNYR_ST from dual;
select to_char(to_date(:P_END_TM_PR1,'yyyymm'), 'Month yyyy' ) into MNYR_END from dual;
MNYR := MNYR_ST ||' - '||MNYR_END ;
END IF;
RETURN MNYR;
end;
it is returning error that it is returning invalid values..

Second question is , i m trying to create 2 formula functions. but the second function doesnt allow me to return CHAR, it says u have to have NUMBER, evenif i change it to CHAR..
Does that mean i can have only one CHAR returning formula ?

Please advise,

rk
end ;
Re: creating a formula in oracle reports - error [message #236711 is a reply to message #236432] Thu, 10 May 2007 12:17 Go to previous messageGo to next message
skooman
Messages: 912
Registered: March 2005
Location: Netherlands
Senior Member
Please use code tags next time, like this:
FUNCTION cf_1formula RETURN CHAR IS
  mnyr_st  VARCHAR2(15);
  mnyr_end VARCHAR2(15);
  mnyr     VARCHAR2(35);
BEGIN
  IF :p_st_tm_pr1 = :p_end_tm_pr1
  THEN
    SELECT to_char(to_date(:p_st_tm_pr1
                          ,'yyyymm')
                  ,'Month yyyy')
    INTO   mnyr
    FROM   dual;
  ELSE
    SELECT to_char(to_date(:p_st_tm_pr1
                          ,'yyyymm')
                  ,'Month yyyy')
    INTO   mnyr_st
    FROM   dual;
    SELECT to_char(to_date(:p_end_tm_pr1
                          ,'yyyymm')
                  ,'Month yyyy')
    INTO   mnyr_end
    FROM   dual;
    mnyr := mnyr_st || ' - ' || mnyr_end;
  END IF;
  RETURN mnyr;
END;


Now, what's happening is:
if value 1 is equal to value 2 then fill mnyr with something
else fill mnyr_st and mnyr_end with something
after that, change mnyr to mnyr_st and mnyr concatenated.

So, what happens if the first is true? Exactly, than you fill mnyr, but after that you replace that value with a concatenation of null-null... I would guess that this would not be the desired result.

However, this is probably not causing the error. The format CHAR is. This is a fixed length datatype, with a default lenght of 1 byte. So, change this to varchar2(n), with n being the number of positions you need.
(BTW, probably char(n char) works too, but char is hardly used anymore, varchar2 a kind of standard - have to admit that I'm not sure why... If you want to know for sure I would suggest posting this question on the SQL forum).
Re: creating a formula in oracle reports - error [message #237367 is a reply to message #236432] Mon, 14 May 2007 04:58 Go to previous message
malakolama
Messages: 16
Registered: May 2007
Location: sri lanka
Junior Member

just wondering if this would work for your first problem.. and also in the properties pallete of the formula column the default 10 width should be adjusted to something greater than 35 chars.

FUNCTION cf_1formula RETURN CHAR IS
mnyr_st VARCHAR2(15):=null;
mnyr_end VARCHAR2(15):=null;
mnyr VARCHAR2(35):=null;
BEGIN
IF :p_st_tm_pr1 = :p_end_tm_pr1 THEN
begin
SELECT nvl(to_char(to_date(:p_st_tm_pr1
,'yyyymm')
,'Month yyyy'),null)
INTO mnyr
FROM dual;
return(mnyr);
end;
ELSE
begin
SELECT nvl(to_char(to_date(:p_st_tm_pr1
,'yyyymm')
,'Month yyyy') , null)
INTO mnyr_st
FROM dual;

SELECT nvl( to_char(to_date(:p_end_tm_pr1
,'yyyymm')
,'Month yyyy'),null)
INTO mnyr_end
FROM dual;

mnyr := mnyr_st || ' - ' || mnyr_end;
RETURN mnyr;
end;

END IF;
RETURN null;
END;


for your second problem, i am not sure if i got your problem correctly but could you check if your return type is char as in

ie:FUNCTION cf_2formula RETURN CHAR IS

and also whether in the property palette of cf_2 the datatype is also char. and also whether at all times you are returning a char value from your function.
Previous Topic: Sorting
Next Topic: Alternating pages in printing
Goto Forum:
  


Current Time: Wed Dec 07 12:59:24 CST 2016

Total time taken to generate the page: 0.14454 seconds