Function not working properly

[diehippy]

Hi everyone,

I have setup the following function, it seems to compile ok but when I try to use it will sql it gives me a character to number conversion error what have I done wrong?

FUNCTION get_learnersAgeband
(
PersonCode IN people.person_code%type,
datefrom IN date
) return number
is
nlearnersageband number(3);
nAgeBand varchar2 (10);
begin --01

begin --10

select floor(monthS_BETWEEN(TO_DATE(datefrom), p.date_of_birth)/12)
into nlearnersageband
from
people p
where
p.person_code = PersonCode;

if nlearnersageband between 14 and 16 then
nageband := '14 - 16' ;
elsif nlearnersageband between 16 and 18 then
nageband := '16 - 18' ;
elsif nlearnersageband >= 19 then
nageband := 'Over 19';
elsif nlearnersageband is null then
nageband := '-1';
end if;

exception
when others then
nageband := '-1';
end; --10

return nageband;

end get_learnersageband;--01

If any one can help I would be most grateful

Many Thanks

Diehippy

 

[taupirho]

Well, for starters you're not returning a number are you?
You are returning a varchar2 - ok it contains a number but that's not the point.

Make nAgeBand a number or change the return type of the function to a varchar. See if that fixes your problem.


In order to understand recursion, you must first understand recursion.

 

[jaxtell]

nageband := 'Over 19';

That is obviously not a number. Change it so it is, or change the return type.

-----------------------------------------
I cannot be bought. Find leasing information at

http://www.joshaxtell.com/

 

[diehippy]

Hi taupirho and jaxtell,

Thanks for the info, I would like to show the text in place of a number I am unsure what I need to do?

Many Thanks

Diehippy

 

[diehippy]

Hi,

I have figured it out, sometimes it takes a while

Many Thanks for your help