| creating a formula in oracle reports - error [message #236432] |
Thu, 10 May 2007 00:08  |
rkhatiwala
Messages: 178
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  |
skooman
Messages: 913
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 |
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.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
