Create function help

Toad for Oracle
1

CREATE OR REPLACE FUNCTION FISC_YEAR (P_DATE_VALUE DATE)

RETURN VARCHAR2

IS

BEGIN

WHEN ‘01’ THEN TO_CHAR((V_DATE_VALUE),‘YYYY’)

WHEN ‘02’ THEN TO_CHAR((V_DATE_VALUE),‘YYYY’)

WHEN ‘03’ THEN TO_CHAR((V_DATE_VALUE),‘YYYY’)

WHEN ‘04’ THEN to_char(to_number(to_char((V_DATE_VALUE), ‘YYYY’)) + 1)

WHEN ‘05’ THEN to_char(to_number(to_char((V_DATE_VALUE), ‘YYYY’)) + 1)

WHEN ‘06’ THEN to_char(to_number(to_char((V_DATE_VALUE), ‘YYYY’)) + 1)

WHEN ‘07’ THEN to_char(to_number(to_char((V_DATE_VALUE), ‘YYYY’)) + 1)

WHEN ‘08’ THEN to_char(to_number(to_char((V_DATE_VALUE), ‘YYYY’)) + 1)

WHEN ‘09’ THEN to_char(to_number(to_char((V_DATE_VALUE), ‘YYYY’))

I’m a newbie trying to create a function to use that returns the FY from a date. I have plans to use this inseveral ways but I keep geting a compile error. Any suggestions?

V_DATE_VALUE DATE := P_DATE_VALUE DATE;CASE TO_CHAR((V_DATE_VALUE),‘MM’)

2

Try this

CREATE OR REPLACE FUNCTION FISC_YEAR (P_DATE_VALUE DATE) RETURN VARCHAR2 AS
V_DATE_VALUE DATE := P_DATE_VALUE;
v_return_value varchar2(4);
BEGIN

CASE
WHEN TO_CHAR(V_DATE_VALUE,'MM') = '01' THEN
    v_return_value := TO_CHAR((V_DATE_VALUE),'YYYY');
WHEN TO_CHAR(V_DATE_VALUE,'MM') = '02' THEN
    v_return_value := TO_CHAR((V_DATE_VALUE),'YYYY');
WHEN TO_CHAR(V_DATE_VALUE,'MM') = '03' THEN
    v_return_value := TO_CHAR((V_DATE_VALUE),'YYYY');
WHEN TO_CHAR(V_DATE_VALUE,'MM') = '04' THEN
    v_return_value := TO_CHAR(TO_NUMBER(TO_CHAR((V_DATE_VALUE), 'YYYY')) + 1);
WHEN TO_CHAR(V_DATE_VALUE,'MM') = '05' THEN
    v_return_value := TO_CHAR(TO_NUMBER(TO_CHAR((V_DATE_VALUE), 'YYYY')) + 1);
WHEN TO_CHAR(V_DATE_VALUE,'MM') = '06' THEN
    v_return_value := TO_CHAR(TO_NUMBER(TO_CHAR((V_DATE_VALUE), 'YYYY')) + 1);
WHEN TO_CHAR(V_DATE_VALUE,'MM') = '07' THEN
    v_return_value := TO_CHAR(TO_NUMBER(TO_CHAR((V_DATE_VALUE), 'YYYY')) + 1);
WHEN TO_CHAR(V_DATE_VALUE,'MM') = '08' THEN
    v_return_value := TO_CHAR(TO_NUMBER(TO_CHAR((V_DATE_VALUE), 'YYYY')) + 1);
WHEN TO_CHAR(V_DATE_VALUE,'MM') = '09' THEN
    v_return_value := TO_CHAR(TO_NUMBER(TO_CHAR((V_DATE_VALUE), 'YYYY')) + 1);
WHEN TO_CHAR(V_DATE_VALUE,'MM') = '10' THEN
    v_return_value := TO_CHAR(TO_NUMBER(TO_CHAR((V_DATE_VALUE), 'YYYY')) + 1);
WHEN TO_CHAR(V_DATE_VALUE,'MM') = '11' THEN
    v_return_value := TO_CHAR(TO_NUMBER(TO_CHAR((V_DATE_VALUE), 'YYYY')) + 1);
WHEN TO_CHAR(V_DATE_VALUE,'MM') = '12' THEN
    v_return_value := TO_CHAR(TO_NUMBER(TO_CHAR((V_DATE_VALUE), 'YYYY')) + 1);
ELSE
	-- unnecessary because TO_CHAR(V_DATE_VALUE,'MM') will fail if other values
    raise_application_error(-20010,'ERROR');

END CASE;

RETURN v_return_value;

END;

3

A condensed version of what you’re trying to achieve.

 create or replace function fisc_year (v_date_value date)
return number as
begin
if extract(month from v_date_value) between 1 and 3
then return extract(year from v_date_value);
else
return extract(year from v_date_value) + 1;
end if;
return null;
end;
/
4

Even more condensed:

create or replace function fisc_year (v_date_value date)
return number as
begin
return extract(year from add_months(v_date_value,9));
end;

or, if you prefer:

create or replace function fisc_year (v_date_value date)
return number as
begin
return to_char(add_months(v_date_value,9),‘YYYY’);
end;