|
Code:
- SET PATH *LIBL ;
-
- CREATE PROCEDURE QWQCENT.CREATE_DATE_TABLE (
- IN THELIB VARCHAR(128) DEFAULT 'QWQCENT' ,
- IN THETABLE VARCHAR(126) DEFAULT 'DATE_DIM' ,
- IN START_YEAR INTEGER DEFAULT ( QSYS2 . YEAR ( CURRENT_DATE ) ) ,
- IN END_YEAR INTEGER DEFAULT ( QSYS2 . YEAR ( CURRENT_DATE ) ) )
- DYNAMIC RESULT SETS 1
- LANGUAGE SQL
- SPECIFIC QWQCENT.CRTDATTBL
- NOT DETERMINISTIC
- MODIFIES SQL DATA
- CALLED ON NULL INPUT
- SYSTEM_TIME SENSITIVE NO
- SET OPTION ALWBLK = *ALLREAD ,
- ALWCPYDTA = *OPTIMIZE ,
- COMMIT = *NONE ,
- DECRESULT = (31, 31, 00) ,
- DFTRDBCOL = QWQCENT ,
- DLYPRP = *NO ,
- DYNDFTCOL = *NO ,
- DYNUSRPRF = *USER ,
- SRTSEQ = *HEX ,
- TGTRLS = V7R3M0 ,
- USRPRF = *USER
- BEGIN DECLARE D_SQL VARCHAR ( 10000 ) ; DECLARE MSG VARCHAR ( 100 ) ; DECLARE FINALVIEW VARCHAR ( 128 ) ; DECLARE FINALTABLE VARCHAR ( 128 ) ; DECLARE ERROR_OCCURRED INT ; DECLARE TERMINATE_ON_ERROR INT ; DECLARE NOT_FOUND INTEGER ; DECLARE C1 CURSOR FOR VALUES ( MSG ) ; DECLARE CONTINUE HANDLER FOR SQLEXCEPTION BEGIN IF TERMINATE_ON_ERROR = 1 THEN RESIGNAL ; END IF ; SET ERROR_OCCURRED = 1 ; END ; SET TERMINATE_ON_ERROR = 1 ; IF LOCATE ( '"' , THELIB ) = 0 THEN SET THELIB = UPPER ( THELIB ) ; END IF ; SET THELIB = TRIM ( THELIB ) ; IF THELIB NOT IN ( 'QTEMP' ) THEN SET NOT_FOUND = ( SELECT COUNT ( * ) FROM TABLE ( QSYS2 . OBJECT_STATISTICS ( THELIB , '*LIB' ) ) X ) ; IF NOT_FOUND = 0 THEN SET D_SQL = SUBSTR ( 'Data library not found: ' CONCAT THELIB , 1 , 72 ) ; SIGNAL SQLSTATE '75001' SET MESSAGE_TEXT = D_SQL ; END IF ; END IF ; IF LOCATE ( '"' , THETABLE ) = 0 THEN SET FINALVIEW = TRIM ( UPPER ( THETABLE ) ) ; ELSE SET FINALVIEW = TRIM ( THETABLE ) ; END IF ; IF FINALVIEW = '' OR FINALVIEW IS NULL THEN SET FINALVIEW = 'DATE_DIM' ; END IF ; SET FINALTABLE = CASE WHEN SUBSTR ( FINALVIEW , LENGTH ( FINALVIEW ) , 1 ) = '"' THEN SUBSTR ( FINALVIEW , 1 , LENGTH ( FINALVIEW ) - 1 ) CONCAT '_T"' ELSE FINALVIEW CONCAT '_T' END ; SET NOT_FOUND = ( SELECT COUNT ( * ) FROM QSYS2 . SYSTABLES WHERE TABLE_SCHEMA = THELIB AND TABLE_NAME = FINALVIEW ) ; IF NOT_FOUND > 0 THEN SET D_SQL = SUBSTR ( 'File already exists: ' CONCAT THELIB CONCAT '/' CONCAT FINALVIEW , 1 , 72 ) ; SIGNAL SQLSTATE '75001' SET MESSAGE_TEXT = D_SQL ; END IF ; SET NOT_FOUND = ( SELECT COUNT ( * ) FROM QSYS2 . SYSTABLES WHERE TABLE_SCHEMA = THELIB AND TABLE_NAME = FINALTABLE ) ; IF NOT_FOUND > 0 THEN SET D_SQL = SUBSTR ( 'File already exists: ' CONCAT THELIB CONCAT '/' CONCAT FINALTABLE , 1 , 72 ) ; SIGNAL SQLSTATE '75001' SET MESSAGE_TEXT = D_SQL ; END IF ; IF START_YEAR < 1 THEN SIGNAL SQLSTATE '75001' SET MESSAGE_TEXT = 'Start Year must be 1 or higher' ; END IF ; IF END_YEAR > 9999 THEN SIGNAL SQLSTATE '75001' SET MESSAGE_TEXT = 'End Year must be 9999 or lower' ; END IF ; IF START_YEAR > END_YEAR THEN SIGNAL SQLSTATE '75001' SET MESSAGE_TEXT = 'Start Year must not be larger than End Year' ; END IF ; IF THELIB IN ( 'QTEMP' ) THEN SET TERMINATE_ON_ERROR = 0 ; SET D_SQL = 'DROP TABLE ' CONCAT THELIB CONCAT '.' CONCAT FINALVIEW ; PREPARE DROP_1 FROM D_SQL ; EXECUTE DROP_1 ; SET D_SQL = 'DROP TABLE ' CONCAT THELIB CONCAT '.' CONCAT FINALTABLE ; PREPARE DROP_2 FROM D_SQL ; EXECUTE DROP_2 ; SET TERMINATE_ON_ERROR = 1 ; END IF ; SET D_SQL = 'CREATE TABLE ' CONCAT THELIB CONCAT '.' CONCAT FINALTABLE CONCAT ' ( DATEKEY DATE NOT NULL PRIMARY KEY, "YEAR" INTEGER NOT NULL , "QUARTER" INTEGER NOT NULL CHECK("QUARTER" BETWEEN 1 AND 4), "MONTH" INTEGER NOT NULL CHECK("MONTH" BETWEEN 1 AND 12), "DAY" INTEGER NOT NULL CHECK("DAY" BETWEEN 1 AND 31), "WEEK" INTEGER NOT NULL CHECK("WEEK" BETWEEN 1 AND 54), DAY_OF_WEEK FOR COLUMN DOW INTEGER NOT NULL CHECK(DAY_OF_WEEK BETWEEN 1 AND 7), DAY_OF_YEAR FOR COLUMN DOY INTEGER NOT NULL CHECK(DAY_OF_YEAR BETWEEN 1 AND 366), WEEK_STARTING_DATE FOR COLUMN WEEK_SD DATE NOT NULL, WEEK_ENDING_DATE FOR COLUMN WEEK_ED DATE NOT NULL, FISCAL_YEAR FOR COLUMN FIS_YR INTEGER NOT NULL , FISCAL_QUARTER FOR COLUMN FIS_QTR INTEGER NOT NULL CHECK(FISCAL_QUARTER BETWEEN 1 AND 4), FISCAL_MONTH FOR COLUMN FIS_MTH INTEGER NOT NULL CHECK(FISCAL_MONTH BETWEEN 1 AND 12), YEAR_ISO INTEGER NOT NULL , WEEK_ISO INTEGER NOT NULL CHECK(WEEK_ISO BETWEEN 1 AND 54), DAY_OF_WEEK_ISO FOR COLUMN DOW_ISO INTEGER NOT NULL CHECK(DAY_OF_WEEK_ISO BETWEEN 1 AND 7), WEEK_STARTING_DATE_ISO FOR COLUMN WEEK_SDI DATE NOT NULL, WEEK_ENDING_DATE_ISO FOR COLUMN WEEK_EDI DATE NOT NULL, MONTH_NAME CHAR(13) NOT NULL, DAY_NAME CHAR(13) NOT NULL, ORDINAL_DOW FOR COLUMN ORD_DOW INT NOT NULL CHECK(ORDINAL_DOW BETWEEN 1 AND 5), MONTH_ABBREVIATION FOR COLUMN MONTH_ABV CHAR(3) NOT NULL, QUARTER_NAME FOR COLUMN QTR_NAM CHAR(6) NOT NULL, NORTHERN_SEASON FOR COLUMN SEASON_N CHAR(6) NOT NULL, SOUTHERN_SEASON FOR COLUMN SEASON_S CHAR(6) NOT NULL, HOLIDAY_FLAG FOR COLUMN HOLIDAY CHAR(1) NOT NULL CHECK(HOLIDAY_FLAG IN (''Y'', ''N'')), WEEKEND_FLAG FOR COLUMN WEEKEND CHAR(1) NOT NULL CHECK(WEEKEND_FLAG IN (''Y'', ''N'')), LEAP_YEAR CHAR(1) NOT NULL CHECK(LEAP_YEAR IN (''Y'', ''N'')), DATE_DELIMITED_US FOR COLUMN DATE_D_US CHAR(10) NOT NULL , DATE_DELIMITED_EU FOR COLUMN DATE_D_EU CHAR(10) NOT NULL , ISO_WEEK_DATE FOR COLUMN ISO_WD CHAR(10) NOT NULL, YYYYMMDD_CHAR FOR COLUMN YYYYMMDD_C CHAR(8) NOT NULL UNIQUE, YYYYMMDD_DEC FOR COLUMN YYYYMMDD_D DECIMAL(8, 0) NOT NULL UNIQUE, YYYYMMDD_NUM FOR COLUMN YYYYMMDD_N NUMERIC(8, 0) NOT NULL UNIQUE, YYMMDD_CHAR FOR COLUMN YYMMDD_C CHAR(6) UNIQUE, YYMMDD_DEC DECIMAL(6, 0) UNIQUE, YYMMDD_NUM NUMERIC(6, 0) UNIQUE, DDMMYYYY_CHAR FOR COLUMN DDMMYYYY_C CHAR(8) NOT NULL UNIQUE, DDMMYYYY_DEC FOR COLUMN DDMMYYYY_D DECIMAL(8) NOT NULL UNIQUE, DDMMYYYY_NUM FOR COLUMN DDMMYYYY_N NUMERIC(8) NOT NULL UNIQUE, DDMMYY_CHAR FOR COLUMN DDMMYY_C CHAR(6) UNIQUE, DDMMYY_DEC DECIMAL(6) UNIQUE, DDMMYY_NUM NUMERIC(6) UNIQUE, MMDDYYYY_CHAR FOR COLUMN MMDDYYYY_C CHAR(8) NOT NULL UNIQUE, MMDDYYYY_DEC FOR COLUMN MMDDYYYY_D DECIMAL(8, 0) NOT NULL UNIQUE, MMDDYYYY_NUM FOR COLUMN MMDDYYYY_N NUMERIC(8, 0) NOT NULL UNIQUE, MMDDYY_CHAR FOR COLUMN MMDDYY_C CHAR(6) UNIQUE, MMDDYY_DEC DECIMAL(6, 0) UNIQUE, MMDDYY_NUM NUMERIC(6, 0) UNIQUE, CYYMMDD_CHAR FOR COLUMN CYYMMD_C CHAR(7) UNIQUE, CYYMMDD_DEC FOR COLUMN CYYMMD_D DECIMAL(7, 0) UNIQUE, CYYMMDD_NUM FOR COLUMN CYYMMD_N NUMERIC(7,0) UNIQUE, JULIAN_YYYYDDD_CHAR FOR COLUMN JYYYYDDD_C CHAR(7) NOT NULL UNIQUE, JULIAN_YYYYDDD_DEC FOR COLUMN JYYYYDDD_D DECIMAL(7, 0) NOT NULL UNIQUE, JULIAN_YYYYDDD_NUM FOR COLUMN JULIAN NUMERIC(7, 0) NOT NULL UNIQUE, JULIAN_CYYDDD_CHAR FOR COLUMN JCYYDDD_C CHAR(6) UNIQUE, JULIAN_CYYDDD_DEC FOR COLUMN JCYYDDD_D DECIMAL(6, 0) UNIQUE, JULIAN_CYYDDD_NUM FOR COLUMN JCYYDDD_N NUMERIC(6, 0) UNIQUE, C_FLAG_CHAR FOR COLUMN C_FLAG_C CHAR(1) , C_FLAG_DEC DECIMAL(1,0) , C_FLAG_NUM NUMERIC(1,0) , CC_CHAR CHAR(2) NOT NULL, CC_DEC DECIMAL(2, 0) NOT NULL, CC_NUM NUMERIC(2, 0) NOT NULL, YYYY_CHAR CHAR(4) NOT NULL, YYYY_DEC DECIMAL(4,0) NOT NULL, YYYY_NUM NUMERIC(4,0) NOT NULL, YY_CHAR CHAR(2) NOT NULL, YY_DEC DECIMAL(2,0) NOT NULL, YY_NUM NUMERIC(2,0) NOT NULL, MM_CHAR CHAR(2) NOT NULL, MM_DEC DECIMAL(2,0) NOT NULL, MM_NUM NUMERIC(2,0) NOT NULL, DD_CHAR CHAR(2) NOT NULL, DD_DEC DECIMAL(2,0) NOT NULL, DD_NUM NUMERIC(2,0) NOT NULL, EXCEL_DATE INTEGER UNIQUE, UNIQUE ("YEAR", "MONTH", "DAY"), UNIQUE (FISCAL_YEAR, FISCAL_MONTH, "DAY")) ' ; PREPARE CREATE_1 FROM D_SQL ; EXECUTE CREATE_1 ; SET D_SQL = 'LABEL ON TABLE ' CONCAT THELIB CONCAT '.' CONCAT FINALTABLE CONCAT ' IS ''Date Dimension Table - IBM v1''' ; PREPARE LABEL_1 FROM D_SQL ; EXECUTE LABEL_1 ; SET D_SQL = 'LABEL ON COLUMN ' CONCAT THELIB CONCAT '.' CONCAT FINALTABLE CONCAT '(DATEKEY IS ''Date'' ,"YEAR" IS ''Year'' ,"QUARTER" IS ''Quarter'' ,"MONTH" IS ''Month'' ,"DAY" IS ''Day'' ,"WEEK" IS ''Week'' ,DAY_OF_WEEK IS ''Day of the Week'' ,DAY_OF_YEAR IS ''Day of the Year'' ,WEEK_STARTING_DATE IS ''First Day of the Week'' ,WEEK_ENDING_DATE IS ''Last Day of the Week'' ,FISCAL_YEAR IS ''Fiscal Year'' ,FISCAL_QUARTER IS ''Fiscal Quarter'' ,FISCAL_MONTH IS ''Fiscal Month'' ,YEAR_ISO IS ''ISO Year'' ,WEEK_ISO IS ''ISO Week'' ,DAY_OF_WEEK_ISO IS ''ISO Day of the Week'' ,WEEK_STARTING_DATE_ISO IS ''ISO First Day of the Week'' ,WEEK_ENDING_DATE_ISO IS ''ISO Last Day of the Week'' ,MONTH_NAME IS ''Month Name'' ,DAY_NAME IS ''Day Name'' ,ORDINAL_DOW IS ''Day Name of the Month'' ,MONTH_ABBREVIATION IS ''Month Short Name'' ,QUARTER_NAME IS ''Quarter Name'' ,NORTHERN_SEASON IS ''Northern Season'' ,SOUTHERN_SEASON IS ''Southern Season'' ,HOLIDAY_FLAG IS ''Holiday'' ,WEEKEND_FLAG IS ''Weekend Day'' ,LEAP_YEAR IS ''Leap Year'' ,DATE_DELIMITED_US IS ''Date - US Format'' ,DATE_DELIMITED_EU IS ''Date - EU Format'' ,ISO_WEEK_DATE IS ''Date - ISO Format'' )' ; PREPARE LABEL_2 FROM D_SQL ; EXECUTE LABEL_2 ; SET D_SQL = 'LABEL ON COLUMN ' CONCAT THELIB CONCAT '.' CONCAT FINALTABLE CONCAT '(DATEKEY TEXT IS ''Date'' ,"YEAR" TEXT IS ''Year'' ,"QUARTER" TEXT IS ''Quarter'' ,"MONTH" TEXT IS ''Month'' ,"DAY" TEXT IS ''Day'' ,"WEEK" TEXT IS ''Week'' ,DAY_OF_WEEK TEXT IS ''Day of the Week'' ,DAY_OF_YEAR TEXT IS ''Day of the Year'' ,WEEK_STARTING_DATE TEXT IS ''First Day of the Week'' ,WEEK_ENDING_DATE TEXT IS ''Last Day of the Week'' ,FISCAL_YEAR TEXT IS ''Fiscal Year'' ,FISCAL_QUARTER TEXT IS ''Fiscal Quarter'' ,FISCAL_MONTH TEXT IS ''Fiscal Month'' ,YEAR_ISO TEXT IS ''ISO Year'' ,WEEK_ISO TEXT IS ''ISO Week'' ,DAY_OF_WEEK_ISO TEXT IS ''ISO Day of the Week'' ,WEEK_STARTING_DATE_ISO TEXT IS ''ISO First Day of the Week'' ,WEEK_ENDING_DATE_ISO TEXT IS ''ISO Last Day of the Week'' ,MONTH_NAME TEXT IS ''Month Name'' ,DAY_NAME TEXT IS ''Day Name'' ,ORDINAL_DOW TEXT IS ''Day Name of the Month'' ,MONTH_ABBREVIATION TEXT IS ''Month Short Name'' ,QUARTER_NAME TEXT IS ''Quarter Name'' ,NORTHERN_SEASON TEXT IS ''Northern Season'' ,SOUTHERN_SEASON TEXT IS ''Southern Season'' ,HOLIDAY_FLAG TEXT IS ''Holiday'' ,WEEKEND_FLAG TEXT IS ''Weekend Day'' ,LEAP_YEAR TEXT IS ''Leap Year'' ,DATE_DELIMITED_US TEXT IS ''Date - US Format'' ,DATE_DELIMITED_EU TEXT IS ''Date - EU Format'' ,ISO_WEEK_DATE TEXT IS ''Date - ISO Format'' )' ; PREPARE LABEL_3 FROM D_SQL ; EXECUTE LABEL_3 ; SET D_SQL = 'CREATE VIEW ' CONCAT THELIB CONCAT '.' CONCAT FINALVIEW CONCAT ' AS SELECT ' CONCAT 'DATEKEY ,"YEAR" ,"QUARTER" ,"MONTH" ,"DAY" ,"WEEK" ,DAY_OF_WEEK ,DAY_OF_YEAR ,WEEK_STARTING_DATE ,WEEK_ENDING_DATE ,FISCAL_YEAR ,FISCAL_QUARTER ,FISCAL_MONTH ,YEAR_ISO ,WEEK_ISO ,DAY_OF_WEEK_ISO ,WEEK_STARTING_DATE_ISO ,WEEK_ENDING_DATE_ISO ,MONTH_NAME ,DAY_NAME ,ORDINAL_DOW ,MONTH_ABBREVIATION ,QUARTER_NAME ,NORTHERN_SEASON ,SOUTHERN_SEASON ,HOLIDAY_FLAG ,WEEKEND_FLAG ,LEAP_YEAR ,DATE_DELIMITED_US ,DATE_DELIMITED_EU ,ISO_WEEK_DATE ' CONCAT ', CHAR(CASE WHEN DATEKEY = CURRENT DATE THEN ''Y'' ELSE ''N'' END) CURRENT_DAY , CHAR(CASE WHEN DATEKEY = CURRENT DATE - 1 DAY THEN ''Y'' ELSE ''N'' END) PREVIOUS_DAY , CHAR(CASE WHEN DATEKEY <= CURRENT DATE AND "YEAR" = YEAR(CURRENT DATE) THEN ''Y'' ELSE ''N'' END) CURRENT_YTD , CHAR(CASE WHEN DATEKEY <= CURRENT DATE - 1 YEAR AND "YEAR" = YEAR(CURRENT DATE) - 1 THEN ''Y'' ELSE ''N'' END) CURRENT_YTD_LAST_YEAR , CHAR(CASE WHEN "WEEK" = WEEK(CURRENT DATE) AND "YEAR" = YEAR(CURRENT DATE) THEN ''Y'' ELSE ''N'' END) CURRENT_WEEK , CHAR(CASE WHEN "WEEK" = WEEK(CURRENT DATE - 7 DAYS) AND "YEAR" = YEAR(CURRENT DATE - 7 DAYS) THEN ''Y'' ELSE ''N'' END) PREVIOUS_WEEK, CHAR(CASE WHEN "MONTH" = MONTH(CURRENT DATE) AND "YEAR" = YEAR(CURRENT DATE) THEN ''Y'' ELSE ''N'' END) CURRENT_MONTH , CHAR(CASE WHEN "MONTH" = MONTH(CURRENT DATE- 1 MONTH) AND "YEAR" = YEAR(CURRENT DATE - 1 MONTH) THEN ''Y'' ELSE ''N'' END) PREVIOUS_MONTH , CHAR(CASE WHEN "QUARTER" = QUARTER(CURRENT DATE ) AND "YEAR" = YEAR(CURRENT DATE) THEN ''Y'' ELSE ''N'' END) CURRENT_QUARTER , CHAR(CASE WHEN "QUARTER" = QUARTER(CURRENT DATE - 3 MONTHS) AND "YEAR" = YEAR(CURRENT DATE - 3 MONTHS) THEN ''Y'' ELSE ''N'' END) PREVIOUS_QUARTER , CHAR(CASE WHEN "YEAR" = YEAR(CURRENT DATE) THEN ''Y'' ELSE ''N'' END) CURRENT_YEAR , CHAR(CASE WHEN "YEAR" = YEAR(CURRENT DATE - 1 YEAR) THEN ''Y'' ELSE ''N'' END) PREVIOUS_YEAR , CHAR(CASE WHEN DATEKEY = CURRENT DATE - 1 YEAR THEN ''Y'' ELSE ''N'' END) CURRENT_DAY_LAST_YEAR , CHAR(CASE WHEN "WEEK" = WEEK(CURRENT DATE - 1 YEAR) AND "YEAR" = YEAR(CURRENT DATE - 1 YEAR) THEN ''Y'' ELSE ''N'' END) CURRENT_WEEK_LAST_YEAR , CHAR(CASE WHEN "MONTH" = MONTH(CURRENT DATE - 1 YEAR) AND "YEAR" = YEAR(CURRENT DATE - 1 YEAR) THEN ''Y'' ELSE ''N'' END) CURRENT_MONTH_LAST_YEAR , CHAR(CASE WHEN "QUARTER" = QUARTER( CURRENT DATE - 1 YEAR) AND "YEAR" = YEAR(CURRENT DATE - 1 YEAR) THEN ''Y'' ELSE ''N'' END) CURRENT_QUARTER_LAST_YEAR ' CONCAT ' ,YYYYMMDD_CHAR ,YYYYMMDD_DEC ,YYYYMMDD_NUM ,YYMMDD_CHAR ,YYMMDD_DEC ,YYMMDD_NUM ,DDMMYYYY_CHAR ,DDMMYYYY_DEC ,DDMMYYYY_NUM ,DDMMYY_CHAR ,DDMMYY_DEC ,DDMMYY_NUM ,MMDDYYYY_CHAR ,MMDDYYYY_DEC ,MMDDYYYY_NUM ,MMDDYY_CHAR ,MMDDYY_DEC ,MMDDYY_NUM ,CYYMMDD_CHAR ,CYYMMDD_DEC ,CYYMMDD_NUM ,JULIAN_YYYYDDD_CHAR ,JULIAN_YYYYDDD_DEC ,JULIAN_YYYYDDD_NUM ,JULIAN_CYYDDD_CHAR ,JULIAN_CYYDDD_DEC ,JULIAN_CYYDDD_NUM ,C_FLAG_CHAR ,C_FLAG_DEC ,C_FLAG_NUM ,CC_CHAR ,CC_DEC ,CC_NUM ,YYYY_CHAR ,YYYY_DEC ,YYYY_NUM ,YY_CHAR ,YY_DEC ,YY_NUM ,MM_CHAR ,MM_DEC ,MM_NUM ,DD_CHAR ,DD_DEC ,DD_NUM ,EXCEL_DATE ' CONCAT ' FROM ' CONCAT THELIB CONCAT '.' CONCAT FINALTABLE CONCAT ' A' ; PREPARE CREATE_2 FROM D_SQL ; EXECUTE CREATE_2 ; SET D_SQL = 'LABEL ON TABLE ' CONCAT THELIB CONCAT '.' CONCAT FINALVIEW CONCAT ' IS ''Date Dimension Table - IBM v1''' ; PREPARE LABELV_1 FROM D_SQL ; EXECUTE LABELV_1 ; SET D_SQL = 'LABEL ON COLUMN ' CONCAT THELIB CONCAT '.' CONCAT FINALVIEW CONCAT '(CURRENT_DAY IS ''Today'' ,PREVIOUS_DAY IS ''Yesterday'' ,CURRENT_YTD IS ''Part of Current Year to Date'' ,CURRENT_YTD_LAST_YEAR IS ''Part of Last Year to Date'' ,CURRENT_WEEK IS ''Current Week'' ,PREVIOUS_WEEK IS ''Last Week'' ,CURRENT_MONTH IS ''Current Month'' ,PREVIOUS_MONTH IS ''Last Month'' ,CURRENT_QUARTER IS ''Current Quarter'' ,PREVIOUS_QUARTER IS ''Last Quarter'' ,CURRENT_YEAR IS ''Current Year'' ,PREVIOUS_YEAR IS ''Last Year'' ,CURRENT_DAY_LAST_YEAR IS ''Same Day as Today Last Year'' ,CURRENT_WEEK_LAST_YEAR IS ''Same Week as Current Week Last Year'' ,CURRENT_MONTH_LAST_YEAR IS ''Same Month as Current Month Last Year'' ,CURRENT_QUARTER_LAST_YEAR IS ''Same Quarter as Current Quarter Last Year'' )' ; PREPARE LABELV_2 FROM D_SQL ; EXECUTE LABELV_2 ; SET D_SQL = 'LABEL ON COLUMN ' CONCAT THELIB CONCAT '.' CONCAT FINALVIEW CONCAT '(CURRENT_DAY TEXT IS ''Today'' ,PREVIOUS_DAY TEXT IS ''Yesterday'' ,CURRENT_YTD TEXT IS ''Part of Current Year to Date'' ,CURRENT_YTD_LAST_YEAR TEXT IS ''Part of Last Year to Date'' ,CURRENT_WEEK TEXT IS ''Current Week'' ,PREVIOUS_WEEK TEXT IS ''Last Week'' ,CURRENT_MONTH TEXT IS ''Current Month'' ,PREVIOUS_MONTH TEXT IS ''Last Month'' ,CURRENT_QUARTER TEXT IS ''Current Quarter'' ,PREVIOUS_QUARTER TEXT IS ''Last Quarter'' ,CURRENT_YEAR TEXT IS ''Current Year'' ,PREVIOUS_YEAR TEXT IS ''Last Year'' ,CURRENT_DAY_LAST_YEAR TEXT IS ''Same Day as Today Last Year'' ,CURRENT_WEEK_LAST_YEAR TEXT IS ''Same Week as Current Week Last Year'' ,CURRENT_MONTH_LAST_YEAR TEXT IS ''Same Month as Current Month Last Year'' ,CURRENT_QUARTER_LAST_YEAR TEXT IS ''Same Quarter as Current Quarter Last Year'' )' ; PREPARE LABELV_3 FROM D_SQL ; EXECUTE LABELV_3 ; SET TERMINATE_ON_ERROR = 0 ; SET D_SQL = 'CREATE OR REPLACE ALIAS QTEMP.DATE_DIM FOR ' CONCAT THELIB CONCAT '.' CONCAT FINALTABLE ; PREPARE ALIAS_1 FROM D_SQL ; EXECUTE ALIAS_1 ; INSERT INTO QTEMP . DATE_DIM ( DATEKEY , "YEAR" , "QUARTER" , "MONTH" , "DAY" , "WEEK" , DAY_OF_WEEK , DAY_OF_YEAR , WEEK_STARTING_DATE , WEEK_ENDING_DATE , FISCAL_YEAR , FISCAL_QUARTER , FISCAL_MONTH , YEAR_ISO , WEEK_ISO , DAY_OF_WEEK_ISO , WEEK_STARTING_DATE_ISO , WEEK_ENDING_DATE_ISO , MONTH_NAME , DAY_NAME , ORDINAL_DOW , MONTH_ABBREVIATION , QUARTER_NAME , NORTHERN_SEASON , SOUTHERN_SEASON , HOLIDAY_FLAG , WEEKEND_FLAG , LEAP_YEAR , DATE_DELIMITED_US , DATE_DELIMITED_EU , ISO_WEEK_DATE , YYYYMMDD_CHAR , YYYYMMDD_DEC , YYYYMMDD_NUM , YYMMDD_CHAR , YYMMDD_DEC , YYMMDD_NUM , DDMMYYYY_CHAR , DDMMYYYY_DEC , DDMMYYYY_NUM , DDMMYY_CHAR , DDMMYY_DEC , DDMMYY_NUM , MMDDYYYY_CHAR , MMDDYYYY_DEC , MMDDYYYY_NUM , MMDDYY_CHAR , MMDDYY_DEC , MMDDYY_NUM , CYYMMDD_CHAR , CYYMMDD_DEC , CYYMMDD_NUM , JULIAN_YYYYDDD_CHAR , JULIAN_YYYYDDD_DEC , JULIAN_YYYYDDD_NUM , JULIAN_CYYDDD_CHAR , JULIAN_CYYDDD_DEC , JULIAN_CYYDDD_NUM , C_FLAG_CHAR , C_FLAG_DEC , C_FLAG_NUM , CC_CHAR , CC_DEC , CC_NUM , YYYY_CHAR , YYYY_DEC , YYYY_NUM , YY_CHAR , YY_DEC , YY_NUM , MM_CHAR , MM_DEC , MM_NUM , DD_CHAR , DD_DEC , DD_NUM , EXCEL_DATE ) WITH RCTE ( D ) AS ( SELECT D FROM TABLE ( VALUES ( DATE ( RIGHT ( '0000' CONCAT RTRIM ( CHAR ( START_YEAR ) ) , 4 ) CONCAT '-01-01' ) ) ) X ( D ) UNION ALL SELECT CASE WHEN RCTE . D = '0300-02-28' THEN DATE ( '0300-03-02' ) ELSE RCTE . D + 1 DAY END FROM RCTE WHERE D < DATE ( RIGHT ( '0000' CONCAT RTRIM ( CHAR ( END_YEAR ) ) , 4 ) CONCAT '-12-31' ) ) , RCTE_WITH_DEFAULT ( D ) AS ( SELECT D FROM RCTE ) , EXPANDED1 AS ( SELECT D , YEAR ( D ) IYEAR , MONTH ( D ) IMONTH , DAY ( D ) IDAY , DAYOFWEEK_ISO ( D ) IDOW_ISO FROM RCTE_WITH_DEFAULT ) , EXPANDED AS ( SELECT E . * , CASE WHEN IYEAR BETWEEN 1900 AND 1999 THEN 0 WHEN IYEAR BETWEEN 2000 AND 2099 THEN 1 ELSE NULL END C_FLAG , RIGHT ( '0000' CONCAT RTRIM ( CHAR ( IYEAR ) ) , 4 ) CYEAR , DAYOFWEEK ( D ) IDOW , DAYOFYEAR ( D ) IDOY , INT ( VARCHAR_FORMAT ( TIMESTAMP ( D , TIME ( '00:00:00' ) ) , 'IYYY' ) ) IYEAR_ISO , WEEK_ISO ( D ) IWEEK_ISO , RIGHT ( '00' CONCAT RTRIM ( CHAR ( IMONTH ) ) , 2 ) CMONTH , RIGHT ( '00' CONCAT RTRIM ( CHAR ( IDAY ) ) , 2 ) CDAY , CASE WHEN IDOW_ISO > 5 THEN 'Y' ELSE 'N' END WEEKEND , CASE WHEN IMONTH = 1 AND IDAY = 1 THEN 'Y' WHEN IMONTH = 5 AND IDOW_ISO = 1 AND IDAY BETWEEN 25 AND 31 THEN 'Y' WHEN IMONTH = 7 AND IDAY = 4 THEN 'Y' /* July 4th */ WHEN IMONTH = 9 AND IDOW_ISO = 1 AND IDAY BETWEEN 1 AND 7 THEN 'Y' /* Labor Day - first Monday in September */ WHEN IMONTH = 11 AND IDOW_ISO = 4 AND IDAY BETWEEN 22 AND 28 THEN 'Y' /* Thanksgiving - 4th Thursday in Nov */ WHEN IMONTH = 12 AND IDAY = 25 THEN 'Y' /* Christmas */ ELSE 'N' END HOLIDAY FROM EXPANDED1 E ) SELECT D , IYEAR , QUARTER ( D ) , IMONTH , DAY ( D ) , WEEK ( D ) , IDOW , IDOY , CASE WHEN D > '0001-01-06' THEN D + ( 1 - IDOW ) DAYS ELSE DATE ( '0001-01-01' ) END , CASE WHEN D < '9999-12-26' THEN D + ( 7 - IDOW ) DAYS ELSE DATE ( '9999-12-31' ) END , IYEAR + CASE WHEN IMONTH > 6 THEN 1 ELSE 0 END /* Fiscal that starts July 1 */ , QUARTER ( D ) + CASE WHEN IMONTH > 6 THEN - 2 ELSE 2 END /* Fiscal that starts July 1 */ , MONTH ( D ) + CASE WHEN IMONTH > 6 THEN - 6 ELSE 6 END /* Fiscal that starts July 1 */ , IYEAR_ISO , IWEEK_ISO , IDOW_ISO , D + ( 1 - IDOW_ISO ) DAYS , CASE WHEN D < '9999-12-27' THEN D + ( 7 - IDOW_ISO ) DAYS ELSE DATE ( '9999-12-31' ) END , MONTHNAME ( D ) , DAYNAME ( D ) , ( DAY ( D ) - 1 ) / 7 + 1 , UPPER ( SUBSTR ( MONTHNAME ( D ) , 1 , 3 ) ) , CYEAR CONCAT 'Q' CONCAT CHAR ( QUARTER ( D ) ) , CASE WHEN IMONTH IN ( 1 , 2 ) OR ( IMONTH = 3 AND DAY ( D ) < 21 ) OR ( IMONTH = 12 AND DAY ( D ) >= 21 ) THEN 'Winter' WHEN IMONTH IN ( 3 , 4 , 5 ) OR ( IMONTH = 6 AND DAY ( D ) < 21 ) THEN 'Spring' WHEN IMONTH IN ( 6 , 7 , 8 ) OR ( IMONTH = 9 AND DAY ( D ) < 21 ) THEN 'Summer' ELSE 'Autumn' END , CASE WHEN IMONTH IN ( 1 , 2 , 12 ) OR ( IMONTH = 3 AND DAY ( D ) < 21 ) OR ( IMONTH = 12 AND DAY ( D ) >= 21 ) THEN 'Summer' WHEN IMONTH IN ( 3 , 4 , 5 ) OR ( IMONTH = 6 AND DAY ( D ) < 21 ) THEN 'Autumn' WHEN IMONTH IN ( 6 , 7 , 8 ) OR ( IMONTH = 9 AND DAY ( D ) < 21 ) THEN 'Winter' ELSE 'Spring' END , HOLIDAY , WEEKEND , CASE WHEN DAYOFYEAR ( DATE ( CYEAR CONCAT '-12-31' ) ) = 366 THEN 'Y' ELSE 'N' END , CYEAR CONCAT '/' CONCAT CMONTH CONCAT '/' CONCAT CDAY , CYEAR CONCAT '-' CONCAT CMONTH CONCAT '-' CONCAT CDAY , RIGHT ( '000' CONCAT TRIM ( CHAR ( IYEAR_ISO ) ) , 4 ) CONCAT '-W' CONCAT RIGHT ( '0' CONCAT RTRIM ( IWEEK_ISO ) , 2 ) CONCAT '-' CONCAT RTRIM ( IDOW_ISO ) , CYEAR CONCAT CMONTH CONCAT CDAY , IYEAR * 10000 + IMONTH * 100 + IDAY , IYEAR * 10000 + IMONTH * 100 + IDAY , CASE WHEN D BETWEEN '1940-01-01' AND '2039-12-31' THEN RIGHT ( CYEAR , 2 ) CONCAT CMONTH CONCAT CDAY ELSE NULL END , CASE WHEN D BETWEEN '1940-01-01' AND '2039-12-31' THEN MOD ( IYEAR , 100 ) * 10000 + IMONTH * 100 + IDAY ELSE NULL END , CASE WHEN D BETWEEN '1940-01-01' AND '2039-12-31' THEN MOD ( IYEAR , 100 ) * 10000 + IMONTH * 100 + IDAY ELSE NULL END , CDAY CONCAT CMONTH CONCAT CYEAR , IDAY * 1000000 + IMONTH * 10000 + IYEAR , IDAY * 1000000 + IMONTH * 10000 + IYEAR , CASE WHEN D BETWEEN '1940-01-01' AND '2039-12-31' THEN CDAY CONCAT CMONTH CONCAT RIGHT ( CYEAR , 2 ) ELSE NULL END , CASE WHEN D BETWEEN '1940-01-01' AND '2039-12-31' THEN IDAY * 10000 + IMONTH * 100 + MOD ( IYEAR , 100 ) ELSE NULL END , CASE WHEN D BETWEEN '1940-01-01' AND '2039-12-31' THEN IDAY * 10000 + IMONTH * 100 + MOD ( IYEAR , 100 ) ELSE NULL END , CMONTH CONCAT CDAY CONCAT CYEAR , IMONTH * 1000000 + IDAY * 10000 + IYEAR , IMONTH * 1000000 + IDAY * 10000 + IYEAR , CASE WHEN D BETWEEN '1940-01-01' AND '2039-12-31' THEN CMONTH CONCAT CDAY CONCAT RIGHT ( CYEAR , 2 ) ELSE NULL END , CASE WHEN D BETWEEN '1940-01-01' AND '2039-12-31' THEN IMONTH * 10000 + IDAY * 100 + MOD ( IYEAR , 100 ) ELSE NULL END , CASE WHEN D BETWEEN '1940-01-01' AND '2039-12-31' THEN IMONTH * 10000 + IDAY * 100 + MOD ( IYEAR , 100 ) ELSE NULL END , RTRIM ( CHAR ( C_FLAG ) ) CONCAT RIGHT ( CYEAR , 2 ) CONCAT CMONTH CONCAT CDAY , C_FLAG * 1000000 /* century */ + MOD ( IYEAR , 100 ) * 10000 /* two digit year */ + IMONTH * 100 + IDAY , C_FLAG * 1000000 /* century */ + MOD ( IYEAR , 100 ) * 10000 /* two digit year */ + IMONTH * 100 + IDAY , RIGHT ( '0000000' CONCAT RTRIM ( CHAR ( IYEAR * 1000 + IDOY ) ) , 7 ) , IYEAR * 1000 + IDOY , IYEAR * 1000 + IDOY , CASE WHEN D BETWEEN '1900-01-01' AND '2099-12-31' THEN RIGHT ( '0000000' CONCAT RTRIM ( CHAR ( ( INT ( D - DATE ( '01/01/1900' ) ) / 10000 ) * 1000 + IDOY ) ) , 6 ) ELSE NULL END , CASE WHEN D BETWEEN '1900-01-01' AND '2099-12-31' THEN ( INT ( D - DATE ( '01/01/1900' ) ) / 10000 ) * 1000 + IDOY ELSE NULL END , CASE WHEN D BETWEEN '1900-01-01' AND '2099-12-31' THEN ( INT ( D - DATE ( '01/01/1900' ) ) / 10000 ) * 1000 + IDOY ELSE NULL END , CHAR ( C_FLAG ) , C_FLAG , C_FLAG , SUBSTR ( CYEAR , 1 , 2 ) , IYEAR / 100 , IYEAR / 100 , CYEAR , IYEAR , IYEAR , RIGHT ( CYEAR , 2 ) , MOD ( IYEAR , 100 ) , MOD ( IYEAR , 100 ) , CMONTH , IMONTH , IMONTH , CDAY , IDAY , IDAY , CASE WHEN D >= '1900-01-01' THEN ( INT ( DAYS ( D ) - DAYS ( DATE ( '01/01/1900' ) ) + 2 ) - ( CASE WHEN D <= '1900-02-28' THEN 1 ELSE 0 END ) ) ELSE NULL END FROM EXPANDED ; SET MSG = 'Table ' CONCAT THELIB CONCAT '/' CONCAT FINALVIEW CONCAT ' created and loaded with ' CONCAT RTRIM ( CHAR ( ( SELECT COUNT ( * ) FROM QTEMP . DATE_DIM ) ) ) CONCAT ' rows.' ; OPEN C1 ; END ;
-
- GRANT EXECUTE
- ON SPECIFIC PROCEDURE QWQCENT.CRTDATTBL
- TO PUBLIC ;
-
- GRANT ALTER , EXECUTE
- ON SPECIFIC PROCEDURE QWQCENT.CRTDATTBL
- TO QSYS WITH GRANT OPTION ;
-
-
- SET PATH *LIBL ;
-
- CREATE PROCEDURE QWQCENT.LOAD_DATE_CONVERSION_TABLE ( )
- LANGUAGE SQL
- SPECIFIC QWQCENT.LOAD_DATE_CONVERSION_TABLE
- NOT DETERMINISTIC
- MODIFIES SQL DATA
- CALLED ON NULL INPUT
- SET OPTION ALWBLK = *ALLREAD ,
- ALWCPYDTA = *OPTIMIZE ,
- COMMIT = *NONE ,
- DECRESULT = (31, 31, 00) ,
- DFTRDBCOL = QWQCENT ,
- DLYPRP = *NO ,
- DYNDFTCOL = *NO ,
- DYNUSRPRF = *USER ,
- RDBCNNMTH = *RUW ,
- SRTSEQ = *HEX
- BEGIN
-
- DECLARE VAR_DATE DATE DEFAULT NULL ;
- DECLARE VAR_JDE_JULIAN_DATE DECIMAL ( 6 , 0 ) ;
- DECLARE VAR_JDE_JULIAN_DATE_CHAR CHAR ( 6 ) ;
- DECLARE VAR_DATE_MDYY_DEC DECIMAL ( 8 , 0 ) ;
- DECLARE VAR_DATE_MDYY_ZONED NUMERIC ( 8 , 0 ) ;
- DECLARE VAR_DATE_MDYY_CHAR CHAR ( 8 ) ;
- DECLARE VAR_DATE_YYMD_DEC DECIMAL ( 8 , 0 ) ;
- DECLARE VAR_DATE_YYMD_ZONED NUMERIC ( 8 , 0 ) ;
- DECLARE VAR_DATE_YYMD_CHAR CHAR ( 8 ) ;
-
- DECLARE VAR_DATE_MDY_DEC DECIMAL ( 6 , 0 ) ;
- DECLARE VAR_DATE_MDY_ZONED NUMERIC ( 6 , 0 ) ;
- DECLARE VAR_DATE_MDY_CHAR CHAR ( 6 ) ;
- DECLARE VAR_DATE_YMD_DEC DECIMAL ( 6 , 0 ) ;
- DECLARE VAR_DATE_YMD_ZONED NUMERIC ( 6 , 0 ) ;
- DECLARE VAR_DATE_YMD_CHAR CHAR ( 6 ) ;
-
- DECLARE VAR_CC_CHAR CHAR ( 2 ) ;
- DECLARE VAR_YY_CHAR CHAR ( 2 ) ;
- DECLARE VAR_MM_CHAR CHAR ( 2 ) ;
- DECLARE VAR_DD_CHAR CHAR ( 2 ) ;
- DECLARE VAR_YEAR INTEGER ;
- DECLARE VAR_DOW INTEGER ;
- DECLARE VAR_DOW_ISO INTEGER ;
- DECLARE VAR_DOY INTEGER ;
- DECLARE VAR_WOY INTEGER ;
- DECLARE VAR_WOY_ISO INTEGER ;
- DECLARE VAR_QOY INTEGER ;
- DECLARE VAR_CC NUMERIC ( 2 , 0 ) ;
- DECLARE VAR_YY NUMERIC ( 2 , 0 ) ;
- DECLARE VAR_MM NUMERIC ( 2 , 0 ) ;
- DECLARE VAR_DD NUMERIC ( 2 , 0 ) ;
- DECLARE VAR_CCYYMM NUMERIC ( 6 , 0 ) ;
- DECLARE VAR_DAY_NAME CHAR ( 9 ) ;
- DECLARE VAR_QUARTER_NAME CHAR ( 6 ) ;
- DECLARE VAR_WEEKEND CHAR ( 1 ) ;
- DECLARE VAR_HOLIDAY CHAR ( 1 ) ;
- DECLARE VAR_DAY_BEFORE_HOLIDAY CHAR ( 1 ) ;
- DECLARE VAR_DAY_AFTER_HOLIDAY CHAR ( 1 ) ;
- DECLARE VAR_FULL_MOON CHAR ( 1 ) ;
- DECLARE VAR_SEASON CHAR ( 6 ) ;
- DECLARE VAR_FISCAL_YEAR INTEGER ;
- DECLARE VAR_FISCAL_QUARTER INTEGER ;
- DECLARE VAR_MONTH_NAME CHAR ( 9 ) ;
- DECLARE VAR_MONTH_ABRV CHAR ( 3 ) ;
- DECLARE VAR_JULIAN NUMERIC ( 7 , 0 ) ;
- DECLARE VAR_EXCEL_DATE INTEGER ;
- DECLARE VAR_CYYMMDD_DEC DECIMAL ( 7 , 0 ) ;
- DECLARE VAR_WEEK_ENDING_DATE DATE DEFAULT NULL ;
- DECLARE VAR_WEEK_STARTING_DATE DATE DEFAULT NULL ;
- DECLARE VAR_SAME_DAY_LAST_YEAR DATE DEFAULT NULL ;
- DECLARE VAR_FLAG CHAR ( 1 ) ;
- DECLARE VAR_NTH_DOW_OF_MONTH INTEGER ;
-
-
- SET VAR_DATE = '01/01/1900' ;
- SET VAR_FLAG = 'N' ;
-
- REPEAT
- SET VAR_YEAR = YEAR ( VAR_DATE ) ;
- SET VAR_EXCEL_DATE = INT ( DAYS ( VAR_DATE ) - DAYS ( DATE ( '01/01/1900' ) ) ) + 1 ;
-
-
-
-
- SET VAR_EXCEL_DATE =
- ( CASE
- WHEN VAR_EXCEL_DATE > 59
- THEN VAR_EXCEL_DATE + 1
- ELSE VAR_EXCEL_DATE
- END ) ;
-
- SET VAR_JDE_JULIAN_DATE = ( INT ( VAR_DATE - DATE ( '01/01/1900' ) ) / 10000 ) * 1000
- + DAYOFYEAR ( VAR_DATE ) ;
- SET VAR_DOW = DAYOFWEEK ( VAR_DATE ) ;
- SET VAR_DOW_ISO = DAYOFWEEK_ISO ( VAR_DATE ) ;
- SET VAR_DOY = DAYOFYEAR ( VAR_DATE ) ;
- SET VAR_WOY = WEEK ( VAR_DATE ) ;
- SET VAR_WOY_ISO = WEEK_ISO ( VAR_DATE ) ;
- SET VAR_QOY = QUARTER ( VAR_DATE ) ;
- SET VAR_CC_CHAR = SUBSTRING ( CHAR ( YEAR ( VAR_DATE ) ) , 1 , 2 ) ;
- SET VAR_YY_CHAR = SUBSTRING ( CHAR ( YEAR ( VAR_DATE ) ) , 3 , 2 ) ;
- SET VAR_MM_CHAR = SUBSTRING ( DIGITS ( MONTH ( VAR_DATE ) ) , 9 , 2 ) ;
- SET VAR_DD_CHAR = SUBSTRING ( DIGITS ( DAY ( VAR_DATE ) ) , 9 , 2 ) ;
- SET VAR_CC = DECIMAL ( SUBSTRING ( CHAR ( YEAR ( VAR_DATE ) ) , 1 , 2 ) ) ;
- SET VAR_YY = DECIMAL ( SUBSTRING ( CHAR ( YEAR ( VAR_DATE ) ) , 3 , 2 ) ) ;
- SET VAR_MM = MONTH ( VAR_DATE ) ;
- SET VAR_DD = DAY ( VAR_DATE ) ;
- SET VAR_CCYYMM = DECIMAL ( VAR_CC_CHAR || VAR_YY_CHAR || VAR_MM_CHAR ) ;
- SET VAR_DATE_MDYY_CHAR = VAR_MM_CHAR || VAR_DD_CHAR || VAR_CC_CHAR || VAR_YY_CHAR ;
- SET VAR_DATE_MDYY_DEC = DECIMAL ( VAR_DATE_MDYY_CHAR ) ;
- SET VAR_DATE_MDYY_ZONED = VAR_DATE_MDYY_DEC ;
- SET VAR_DATE_YYMD_CHAR = ( VAR_CC * 1000000 ) + ( VAR_YY * 10000 )
- + ( VAR_MM * 100 ) + VAR_DD ;
- SET VAR_DATE_YYMD_DEC = DECIMAL ( VAR_DATE_YYMD_CHAR ) ;
- SET VAR_DATE_YYMD_ZONED = VAR_DATE_YYMD_DEC ;
-
- SET VAR_DATE_MDY_CHAR = VAR_MM_CHAR || VAR_DD_CHAR || VAR_YY_CHAR ;
- SET VAR_DATE_MDY_DEC = DECIMAL ( VAR_DATE_MDY_CHAR ) ;
- SET VAR_DATE_MDY_ZONED = VAR_DATE_MDY_DEC ;
- SET VAR_DATE_YMD_CHAR = ( VAR_YY * 10000 )
- + ( VAR_MM * 100 ) + VAR_DD ;
- SET VAR_DATE_YMD_DEC = DECIMAL ( VAR_DATE_YMD_CHAR ) ;
- SET VAR_DATE_YMD_ZONED = VAR_DATE_YMD_DEC ;
-
- SET VAR_CYYMMDD_DEC =
- ( CASE
- WHEN VAR_CC = 19
- THEN ( VAR_YY * 10000 ) + ( VAR_MM * 100 ) + VAR_DD
- ELSE 1000000 + ( VAR_YY * 10000 ) + ( VAR_MM * 100 ) + VAR_DD
- END ) ;
-
- SET VAR_DAY_NAME =
- ( CASE VAR_DOW_ISO
- WHEN 1
- THEN 'Monday'
- WHEN 2
- THEN 'Tuesday'
- WHEN 3
- THEN 'Wednesday'
- WHEN 4
- THEN 'Thursday'
- WHEN 5
- THEN 'Friday'
- WHEN 6
- THEN 'Saturday'
- WHEN 7
- THEN 'Sunday'
- ELSE ''
- END ) ;
-
- SET VAR_QUARTER_NAME = TRIM ( CHAR ( YEAR ( VAR_DATE ) ) ) CONCAT 'Q' CONCAT TRIM ( CHAR ( QUARTER ( VAR_DATE ) ) ) ;
-
- SET VAR_WEEKEND =
- ( CASE VAR_DOW_ISO
- WHEN 6
- THEN 'Y'
- WHEN 7
- THEN 'Y'
- ELSE 'N'
- END ) ;
- SET VAR_HOLIDAY = 'N' ;
- SET VAR_DAY_BEFORE_HOLIDAY = 'N' ;
- SET VAR_DAY_AFTER_HOLIDAY = 'N' ;
- SET VAR_FULL_MOON = 'N' ;
- SET VAR_SEASON =
- ( CASE
- WHEN VAR_MM < 3 OR ( VAR_MM = 3 AND VAR_DD < 21 )
- THEN 'Winter'
- WHEN VAR_MM < 6 OR ( VAR_MM = 6 AND VAR_DD < 21 )
- THEN 'Spring'
- WHEN VAR_MM < 9 OR ( VAR_MM = 9 AND VAR_DD < 21 )
- THEN 'Summer'
- WHEN VAR_MM < 12 OR ( VAR_MM = 12 AND VAR_DD < 21 )
- THEN 'Autumn'
- ELSE 'Winter'
- END ) ;
-
- SET VAR_FISCAL_QUARTER =
- ( CASE
- WHEN VAR_MM < 3
- THEN 4
- WHEN VAR_MM < 6
- THEN 1
- WHEN VAR_MM < 9
- THEN 2
- WHEN VAR_MM < 11
- THEN 3
- ELSE 4
- END ) ;
-
- SET VAR_FISCAL_YEAR =
- ( CASE
- WHEN VAR_MM < 3
- THEN YEAR ( VAR_DATE )
- ELSE YEAR ( VAR_DATE ) + 1
- END ) ;
-
- SET VAR_MONTH_NAME = MONTHNAME ( VAR_DATE ) ;
- SET VAR_MONTH_ABRV =
- ( CASE VAR_MM
- WHEN 1
- THEN 'JAN'
- WHEN 2
- THEN 'FEB'
- WHEN 3
- THEN 'MAR'
- WHEN 4
- THEN 'APR'
- WHEN 5
- THEN 'MAY'
- WHEN 6
- THEN 'JUN'
- WHEN 7
- THEN 'JUL'
- WHEN 8
- THEN 'AUG'
- WHEN 9
- THEN 'SEP'
- WHEN 10
- THEN 'OCT'
- WHEN 11
- THEN 'NOV'
- WHEN 12
- THEN 'DEC'
- ELSE ''
- END ) ;
-
- SET VAR_JULIAN = DECIMAL ( SUBSTRING ( CHAR ( ( YEAR ( VAR_DATE ) * 1000
- + DAYOFYEAR ( VAR_DATE ) ) ) , 1 , 7 ) ) ;
-
- SET VAR_WEEK_ENDING_DATE =
- ( CASE DAYOFWEEK_ISO ( VAR_DATE )
- WHEN 1
- THEN VAR_DATE + 6 DAYS
- WHEN 2
- THEN VAR_DATE + 5 DAYS
- WHEN 3
- THEN VAR_DATE + 4 DAYS
- WHEN 4
- THEN VAR_DATE + 3 DAYS
- WHEN 5
- THEN VAR_DATE + 2 DAYS
- WHEN 6
- THEN VAR_DATE + 1 DAYS
- WHEN 7
- THEN VAR_DATE
- ELSE NULL
- END ) ;
-
- SET VAR_NTH_DOW_OF_MONTH =
- ( CASE
- WHEN VAR_DD <= 7
- THEN 1
- WHEN VAR_DD > 7 AND VAR_DD <= 14
- THEN 2
- WHEN VAR_DD > 14 AND VAR_DD <= 21
- THEN 3
- WHEN VAR_DD > 21 AND VAR_DD <= 28
- THEN 4
- WHEN VAR_DD > 28
- THEN 5
- ELSE 0
- END ) ;
-
- SET VAR_WEEK_STARTING_DATE = VAR_WEEK_ENDING_DATE - 6 DAYS ;
- SET VAR_SAME_DAY_LAST_YEAR = VAR_DATE - 364 DAYS ;
-
- INSERT INTO QWQCENT . DATE_CONV VALUES (
- VAR_DATE ,
- VAR_JDE_JULIAN_DATE ,
- VAR_DATE_MDYY_DEC ,
- VAR_DATE_MDYY_ZONED ,
- VAR_DATE_MDYY_CHAR ,
- VAR_DATE_YYMD_DEC ,
- VAR_DATE_YYMD_ZONED ,
- VAR_DATE_YYMD_CHAR ,
-
- VAR_DATE_MDY_DEC ,
- VAR_DATE_MDY_ZONED ,
- VAR_DATE_MDY_CHAR ,
- VAR_DATE_YMD_DEC ,
- VAR_DATE_YMD_ZONED ,
- VAR_DATE_YMD_CHAR ,
-
- VAR_CC_CHAR ,
- VAR_YY_CHAR ,
- VAR_MM_CHAR ,
- VAR_DD_CHAR ,
- VAR_YEAR ,
- VAR_DOW ,
- VAR_DOW_ISO ,
- VAR_DOY ,
- VAR_WOY ,
- VAR_WOY_ISO ,
- VAR_QOY ,
- VAR_CC ,
- VAR_YY ,
- VAR_MM ,
- VAR_DD ,
- VAR_CCYYMM ,
- VAR_DAY_NAME ,
- VAR_QUARTER_NAME ,
- VAR_WEEKEND ,
- VAR_HOLIDAY ,
- VAR_DAY_BEFORE_HOLIDAY ,
- VAR_DAY_AFTER_HOLIDAY ,
- VAR_FULL_MOON ,
- VAR_SEASON ,
- VAR_FISCAL_YEAR ,
- VAR_FISCAL_QUARTER ,
- VAR_MONTH_NAME ,
- VAR_MONTH_ABRV ,
- VAR_JULIAN ,
- VAR_CYYMMDD_DEC ,
- VAR_EXCEL_DATE ,
- VAR_WEEK_STARTING_DATE ,
- VAR_WEEK_ENDING_DATE ,
- VAR_SAME_DAY_LAST_YEAR ,
- VAR_FLAG ,
- VAR_FLAG ,
- VAR_FLAG ,
- VAR_FLAG ,
- VAR_FLAG ,
- VAR_FLAG ,
- VAR_FLAG ,
- VAR_FLAG ,
- VAR_FLAG ,
- VAR_FLAG ,
- VAR_FLAG ,
- VAR_FLAG ,
- VAR_FLAG ,
- VAR_FLAG ,
- VAR_FLAG ,
- VAR_FLAG ,
- VAR_FLAG ,
- VAR_FLAG ,
- VAR_FLAG ,
- VAR_FLAG ,
- VAR_FLAG ,
- VAR_NTH_DOW_OF_MONTH ) ;
- SET VAR_DATE = VAR_DATE + 1 DAY ;
- UNTIL VAR_DATE > '12/31/2030'
-
- END REPEAT ;
- END ;
-
- GRANT EXECUTE
- ON SPECIFIC PROCEDURE QWQCENT.LOAD_DATE_CONVERSION_TABLE
- TO PUBLIC ;
-
- GRANT ALTER , EXECUTE
- ON SPECIFIC PROCEDURE QWQCENT.LOAD_DATE_CONVERSION_TABLE
- TO QWQADMIN WITH GRANT OPTION ;
-
-
- SET PATH *LIBL ;
-
- CREATE PROCEDURE QWQCENT.UPDATE_DATE_CONVERSION_TABLE ( )
- LANGUAGE SQL
- SPECIFIC QWQCENT.UPDATE_DATE_CONVERSION_TABLE
- NOT DETERMINISTIC
- MODIFIES SQL DATA
- CALLED ON NULL INPUT
- SET OPTION ALWBLK = *ALLREAD ,
- ALWCPYDTA = *OPTIMIZE ,
- COMMIT = *NONE ,
- DECRESULT = (31, 31, 00) ,
- DFTRDBCOL = *NONE ,
- DYNDFTCOL = *NO ,
- DYNUSRPRF = *USER ,
- SRTSEQ = *HEX
- BEGIN
-
- UPDATE QWQCENT . DATE_CONV SET DC_CURRENT_DAY = 'N' WHERE DC_CURRENT_DAY = 'Y' ;
- UPDATE QWQCENT . DATE_CONV SET DC_CURRENT_YTD = 'N' WHERE DC_CURRENT_YTD = 'Y' ;
- UPDATE QWQCENT . DATE_CONV SET DC_CURRENT_YTD_LAST_YEAR = 'N' WHERE DC_CURRENT_YTD_LAST_YEAR = 'Y' ;
- UPDATE QWQCENT . DATE_CONV SET DC_CURRENT_WEEK = 'N' WHERE DC_CURRENT_WEEK = 'Y' ;
- UPDATE QWQCENT . DATE_CONV SET DC_CURRENT_MONTH = 'N' WHERE DC_CURRENT_MONTH = 'Y' ;
- UPDATE QWQCENT . DATE_CONV SET DC_CURRENT_QUARTER = 'N' WHERE DC_CURRENT_QUARTER = 'Y' ;
- UPDATE QWQCENT . DATE_CONV SET DC_CURRENT_YEAR = 'N' WHERE DC_CURRENT_YEAR = 'Y' ;
- UPDATE QWQCENT . DATE_CONV SET DC_CURRENT_DAY_LAST_YEAR = 'N' WHERE DC_CURRENT_DAY_LAST_YEAR = 'Y' ;
- UPDATE QWQCENT . DATE_CONV SET DC_CURRENT_WEEK_LAST_YEAR = 'N' WHERE DC_CURRENT_WEEK_LAST_YEAR = 'Y' ;
- UPDATE QWQCENT . DATE_CONV SET DC_CURRENT_MONTH_LAST_YEAR = 'N' WHERE DC_CURRENT_MONTH_LAST_YEAR = 'Y' ;
- UPDATE QWQCENT . DATE_CONV SET DC_CURRENT_QUARTER_LAST_YEAR = 'N'
- WHERE DC_CURRENT_QUARTER_LAST_YEAR = 'Y' ;
- UPDATE QWQCENT . DATE_CONV SET DC_CURRENT_YEAR_LAST_YEAR = 'N' WHERE DC_CURRENT_YEAR_LAST_YEAR = 'Y' ;
-
- UPDATE QWQCENT . DATE_CONV SET DC_PREVIOUS_DAY = 'N' WHERE DC_PREVIOUS_DAY = 'Y' ;
- UPDATE QWQCENT . DATE_CONV SET DC_PREVIOUS_WEEK = 'N' WHERE DC_PREVIOUS_WEEK = 'Y' ;
- UPDATE QWQCENT . DATE_CONV SET DC_PREVIOUS_MONTH = 'N' WHERE DC_PREVIOUS_MONTH = 'Y' ;
- UPDATE QWQCENT . DATE_CONV SET DC_PREVIOUS_QUARTER = 'N' WHERE DC_PREVIOUS_QUARTER = 'Y' ;
- UPDATE QWQCENT . DATE_CONV SET DC_PREVIOUS_YEAR = 'N' WHERE DC_PREVIOUS_YEAR = 'Y' ;
-
-
-
- UPDATE QWQCENT . DATE_CONV SET DC_CURRENT_DAY = 'Y' WHERE DC_DATE = CURRENT DATE ;
- UPDATE QWQCENT . DATE_CONV SET DC_PREVIOUS_DAY = 'Y' WHERE DC_DATE = CURRENT DATE - 1 DAY ;
- UPDATE QWQCENT . DATE_CONV SET DC_CURRENT_YTD = 'Y' WHERE DC_DATE <= CURRENT DATE AND DC_YEAR = YEAR ( CURRENT DATE ) ;
-
- UPDATE QWQCENT . DATE_CONV SET DC_CURRENT_YTD_LAST_YEAR = 'Y'
- WHERE ( DC_MM < MONTH ( CURRENT DATE ) OR ( DC_MM = MONTH ( CURRENT DATE ) AND DC_DD <= DAY ( CURRENT DATE ) ) )
- AND DC_YEAR = YEAR ( CURRENT DATE ) - 1 ;
-
- UPDATE QWQCENT . DATE_CONV SET DC_CURRENT_WEEK = 'Y' WHERE DC_DATE >=
- ( SELECT DC_WEEK_STARTING_DATE FROM QWQCENT . DATE_CONV WHERE DC_DATE = CURRENT DATE )
- AND DC_DATE <= ( SELECT DC_WEEK_ENDING_DATE FROM QWQCENT . DATE_CONV WHERE DC_DATE = CURRENT DATE ) ;
- UPDATE QWQCENT . DATE_CONV SET DC_PREVIOUS_WEEK = 'Y' WHERE DC_DATE IN
- ( SELECT DC_DATE - 7 DAYS FROM QWQCENT . DATE_CONV WHERE DC_CURRENT_WEEK = 'Y' ) ;
-
-
- UPDATE QWQCENT . DATE_CONV SET DC_CURRENT_MONTH = 'Y'
- WHERE DC_YEAR = YEAR ( CURRENT DATE ) AND DC_MM = MONTH ( CURRENT DATE ) ;
-
- CASE
- WHEN MONTH ( CURRENT DATE ) = 1
- THEN UPDATE QWQCENT . DATE_CONV SET DC_PREVIOUS_MONTH = 'Y'
- WHERE ( DC_YEAR = YEAR ( CURRENT DATE ) - 1 AND DC_MM = 12 ) ;
- ELSE
- UPDATE QWQCENT . DATE_CONV SET DC_PREVIOUS_MONTH = 'Y'
- WHERE ( DC_YEAR = YEAR ( CURRENT DATE ) AND DC_MM = MONTH ( CURRENT DATE ) - 1 ) ;
- END CASE ;
-
- UPDATE QWQCENT . DATE_CONV SET DC_CURRENT_QUARTER = 'Y'
- WHERE DC_YEAR = YEAR ( CURRENT DATE ) AND DC_QOY = QUARTER ( CURRENT DATE ) ; CASE
- WHEN MONTH ( CURRENT DATE ) >= 1 AND MONTH ( CURRENT DATE ) <= 3
- THEN UPDATE QWQCENT . DATE_CONV SET DC_PREVIOUS_QUARTER = 'Y'
- WHERE ( DC_YEAR = YEAR ( CURRENT DATE ) - 1 AND ( DC_MM >= 10 AND DC_MM <= 12 ) ) ;
-
- WHEN MONTH ( CURRENT DATE ) >= 4 AND MONTH ( CURRENT DATE ) <= 6
- THEN UPDATE QWQCENT . DATE_CONV SET DC_PREVIOUS_QUARTER = 'Y'
- WHERE ( DC_YEAR = YEAR ( CURRENT DATE ) AND ( DC_MM >= 1 AND DC_MM <= 3 ) ) ;
- WHEN MONTH ( CURRENT DATE ) >= 7 AND MONTH ( CURRENT DATE ) <= 9
- THEN UPDATE QWQCENT . DATE_CONV SET DC_PREVIOUS_QUARTER = 'Y'
- WHERE ( DC_YEAR = YEAR ( CURRENT DATE ) AND ( DC_MM >= 4 AND DC_MM <= 6 ) ) ;
- ELSE
- UPDATE QWQCENT . DATE_CONV SET DC_PREVIOUS_QUARTER = 'Y'
- WHERE ( DC_YEAR = YEAR ( CURRENT DATE ) AND ( DC_MM >= 7 AND DC_MM <= 9 ) ) ; END CASE ;
-
-
- UPDATE QWQCENT . DATE_CONV SET DC_CURRENT_YEAR = 'Y' WHERE DC_YEAR = YEAR ( CURRENT DATE ) ;
- UPDATE QWQCENT . DATE_CONV SET DC_PREVIOUS_YEAR = 'Y' WHERE DC_YEAR = YEAR ( CURRENT DATE ) - 1 ;
-
- UPDATE QWQCENT . DATE_CONV SET DC_CURRENT_DAY_LAST_YEAR = 'Y' WHERE DC_DATE = CURRENT DATE - 364 DAYS ;
- UPDATE QWQCENT . DATE_CONV SET DC_CURRENT_WEEK_LAST_YEAR = 'Y' WHERE DC_DATE >=
- ( SELECT DC_WEEK_STARTING_DATE FROM QWQCENT . DATE_CONV WHERE DC_DATE = ( CURRENT DATE - 364 DAYS ) )
- AND DC_DATE <= ( SELECT DC_WEEK_ENDING_DATE FROM QWQCENT . DATE_CONV WHERE DC_DATE =
- ( CURRENT DATE - 364 DAYS ) ) ;
- UPDATE QWQCENT . DATE_CONV SET DC_CURRENT_MONTH_LAST_YEAR = 'Y'
- WHERE DC_YEAR = ( YEAR ( CURRENT DATE ) - 1 ) AND DC_MM = MONTH ( CURRENT DATE ) ;
- UPDATE QWQCENT . DATE_CONV SET DC_CURRENT_QUARTER_LAST_YEAR = 'Y'
- WHERE DC_YEAR = ( YEAR ( CURRENT DATE ) - 1 ) AND DC_QOY = QUARTER ( CURRENT DATE ) ;
- UPDATE QWQCENT . DATE_CONV SET DC_CURRENT_YEAR_LAST_YEAR = 'Y'
- WHERE DC_YEAR = YEAR ( CURRENT DATE - 1 YEAR ) ;
-
- END ;
-
- GRANT EXECUTE
- ON SPECIFIC PROCEDURE QWQCENT.UPDATE_DATE_CONVERSION_TABLE
- TO PUBLIC ;
-
- GRANT ALTER , EXECUTE
- ON SPECIFIC PROCEDURE QWQCENT.UPDATE_DATE_CONVERSION_TABLE
- TO QWQADMIN WITH GRANT OPTION ;
-
-
|
|