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
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