Returns (date)
return value (actual_return)
as I dint see any way to call a scalar function from a select statement, I thought of using a table function and calling it in the select statement but this gives an error and searches for a column name after Returns (date..
Bellow is the function
Expand|Select|Wrap|Line Numbers
- CREATE OR REPLACE FUNCTION ACHINST1.AGING (
- X DATE,
- Y INTEGER )
- returns table(date)
- LANGUAGE SQL
- MODIFIES SQL DATA
- begin
- declare counter_insert int;
- declare counter_tat int;
- declare store int;
- declare i int;
- declare creation_day int;
- declare creation_month int;
- declare creation_year int;
- declare expairy_day int;
- declare tat int;
- declare actual_month int;
- declare tat_date varchar(20);
- declare month_days int;
- declare expairy_id int;
- declare return_value varchar(20);
- declare holiday_counter int;
- declare actual_return date;
- declare c1 cursor for
- select length(B.days|| B1.days) from ACHINST1.DMSBUSINESSCALENDAR as B inner join ACHINST1.DMSBUSINESSCALENDAR as B1
- ON B1.BC_MONTH = (CASE WHEN B.BC_MONTH < 11 THEN B.BC_MONTH+1 ELSE 0 END)
- AND B1.BC_YEAR = (CASE WHEN B.BC_MONTH = 11 THEN B.BC_YEAR+1 ELSE B.BC_YEAR END)
- where B.STATUS = 'ACTIVE' and B.calendarkey = 'INR' and B1.CALENDARKEY = 'INR' and B1.status = 'ACTIVE'
- and b.bc_month = actual_month and b.bc_year = creation_year;
- set counter_insert = 1;
- set creation_day = day(x);
- set creation_month = month(x);
- set actual_month = creation_month -1;
- set creation_year = year(x);
- set expairy_day = creation_day;
- set expairy_id=creation_day;
- set tat = y;
- set counter_tat = 1;
- if actual_month = 0
- then set month_days = 31;
- elseif actual_month = 1
- then
- if ((mod(creation_year,4) = 0) AND ((mod(creation_year,100) != 0) OR (mod(creation_year,400) = 0)))
- then set month_days = 29;
- else
- set month_days = 28;
- end if;
- elseif actual_month = 2
- then set month_days = 31;
- elseif actual_month = 3
- then set month_days = 30;
- elseif actual_month = 4
- then set month_days = 31;
- elseif actual_month = 5
- then set month_days = 30;
- elseif actual_month = 6
- then set month_days = 31;
- elseif actual_month = 7
- then set month_days = 31;
- elseif actual_month = 8
- then set month_days = 30;
- elseif actual_month = 9
- then set month_days = 31;
- elseif actual_month = 10
- then set month_days = 30;
- elseif actual_month = 11
- then set month_days = 31;
- end if;
- open c1;
- fetch c1 into store;
- close c1;
- while (counter_insert <= store) do
- insert into achinst1.calender values (counter_insert, (select substr((B.days|| B1.days),counter_insert,1) from ACHINST1.DMSBUSINESSCALENDAR as B inner join ACHINST1.DMSBUSINESSCALENDAR as B1
- ON B1.BC_MONTH = (CASE WHEN B.BC_MONTH < 11 THEN B.BC_MONTH+1 ELSE 0 END)
- AND B1.BC_YEAR = (CASE WHEN B.BC_MONTH = 11 THEN B.BC_YEAR+1 ELSE B.BC_YEAR END)
- where B.STATUS = 'ACTIVE' and B.calendarkey = 'INR' and B1.CALENDARKEY = 'INR' and B1.status = 'ACTIVE'
- and b.bc_month = actual_month and b.bc_year = creation_year));
- set counter_insert = counter_insert+1;
- end while;
- while (counter_tat <= tat) do
- if exists (select value from calender where id = expairy_id and value = 1)
- then
- set counter_tat = counter_tat+1;
- end if;
- if expairy_day = month_days
- then
- if creation_month = 12
- then
- set expairy_day = 1;
- set creation_month = 1;
- set creation_year = creation_year+1;
- else
- set expairy_day = 1;
- set creation_month = creation_month + 1;
- end if;
- else
- set expairy_day = expairy_day+1;
- end if;
- set expairy_id = expairy_id+1;
- end while;
- set holiday_counter = counter_tat + 1;
- while exists (select value from calender where id = expairy_id and value = 0) do
- if expairy_day = month_days
- then
- if creation_month = 12
- then
- set expairy_day = 1;
- set creation_month = 1;
- set creation_year = creation_year+1;
- else
- set expairy_day = 1;
- set creation_month = creation_month + 1;
- end if;
- else
- set expairy_day = expairy_day+1;
- end if;
- set expairy_id = expairy_id+1;
- end while;
- set return_value = (creation_year || '-' ||creation_month || '-' || expairy_day);
- delete from achinst1.CALENDER;
- set actual_return = date(to_date(return_value,'DD-MM-YYYY'));
- return values (actual_return);
- commit;
- end;