CREATE FUNCTION F_YEAR_ALL_DAYS
(THE_DAY VARCHAR(10)
) 
RETURNS TABLE
(ROWID INTEGER,
DETAIL_DATE DATE
)
SPECIFIC SQL071218171441700
LANGUAGE SQL
NOT DETERMINISTIC
READS SQL DATA
STATIC DISPATCH
CALLED ON NULL INPUT
EXTERNAL ACTION
INHERIT SPECIAL REGISTERS
p1:begin atomic
DECLARE v_first_day VARCHAR(10);
SET v_first_day = substr(THE_DAY,1,4)||'-01'||'-01';
RETURN
select T.rowid,T.detail_date
from (
select a.rowid,a.detail_date
from (
select ROW_NUMBER() over() as rowid, (DATE(v_first_day) + ( 
ROW_NUMBER() over() - 1) day ) as detail_date
from table_a) a
where year(a.detail_date) = year(v_first_day)) as T;
end p1@