/*
implement CTE,UDF and Recursive SQL in the Scenario:
giving the parameters year and quarter and
returning the start and end date of the assigned parameters.
運用 Recursive SQL 產生指定日曆天組一個 CTE
包成一個User Define Function.
input paramters: 年,季
output table: two columns of 季的起始日,迄日
*/
/* create function */
CREATE FUNCTION UDF_QUARTER2DATE(nYear VARCHAR(4),
nQuarter INTEGER)
RETURNS TABLE (MINDT INTEGER,
MAXDT INTEGER)
LANGUAGE SQL
SPECIFIC UDF_QUARTER2DATE
BEGIN ATOMIC
RETURN
WITH N(DT) AS
(
SELECT DATE(SUBSTR(nYear,1,4) || '-01-01') DT
FROM SYSIBM.SYSDUMMY1
UNION ALL
SELECT N.DT + 1 DAY
FROM N
WHERE YEAR(N.DT + 1 DAY) <= INT(nYear)
)
SELECT MIN(INT(DT)),MAX(INT(DT))
FROM N
WHERE QUARTER(DT) = nQuarter;
END
/* call function & return result */
SELECT * FROM TABLE(UDF_QUARTER2DATE('2008',3))
MINDT MAXDT
----------- ------------
20080701 20080930
