Pages

Friday, 28 December 2012

BI Direct Useful Tips- A -Date Dimension Data Seeding - Store Procedure

It is very common to have the date dimension pre-seeded. This blog is an attempt to list down the parameter driven store procedure and package to support date dimension data seeding. I believe it covers almost all attributes on date dimension which would be useful for time related analysis of transactional data.

Typical Date Dimension Script

CREATE TABLE W_DAY_D
(
  ROW_WID                    NUMBER(10)         NOT NULL,
  CALENDAR_DATE              DATE               NOT NULL,
  CAL_HALF                   NUMBER(2),
  CAL_MONTH                  NUMBER(2),
  CAL_QTR                    NUMBER(1),
  CAL_TRIMESTER              NUMBER(10),
  CAL_WEEK                   NUMBER(2),
  CAL_YEAR                   NUMBER(4),
  DATASOURCE_NUM_ID          NUMBER(10)         NOT NULL,
  DATE_KEY                   NUMBER(10),
  DAY_AGO_DT                 DATE,
  DAY_AGO_KEY                NUMBER(10),
  DAY_AGO_WID                NUMBER(10),
  DAY_DT                     DATE,
  DAY_NAME                   VARCHAR2(30 CHAR),
  DAY_OF_MONTH               NUMBER(2),
  DAY_OF_WEEK                NUMBER(1),
  DAY_OF_YEAR                NUMBER(3),
  FSCL_DAY_OF_MONTH          NUMBER(2),
  FSCL_DAY_OF_WEEK           NUMBER(2),
  FSCL_DAY_OF_YEAR           NUMBER(3),
  FSCL_HALF                  NUMBER(1),
  FSCL_MONTH                 NUMBER(2),
  FSCL_QTR                   NUMBER(1),
  FSCL_TRIMESTER             NUMBER(10),
  FSCL_WEEK                  NUMBER(2),
  FSCL_YEAR                  NUMBER(4),
  FSCL_FST_DAY_KEY           NUMBER(10),
  HALF_AGO_DT                DATE,
  HALF_AGO_KEY               NUMBER(10),
  HALF_AGO_WID               NUMBER(10),
  INTEGRATION_ID             VARCHAR2(30 CHAR),
  JULIAN_DAY_NUM             NUMBER(10),
  JULIAN_MONTH_NUM           NUMBER(10),
  JULIAN_QTR_NUM             NUMBER(10),
  JULIAN_TER_NUM             NUMBER(10),
  JULIAN_WEEK_NUM            NUMBER(10),
  JULIAN_YEAR_NUM            NUMBER(10),
  MONTH_AGO_DT               DATE,
  MONTH_AGO_KEY              NUMBER(10),
  MONTH_AGO_WID              NUMBER(10),
  MONTH_NAME                 VARCHAR2(30 CHAR),
  PERIOD_KEY                 NUMBER(10),
  PER_NAME_FSCL_HALF         VARCHAR2(50 CHAR),
  PER_NAME_FSCL_MNTH         VARCHAR2(50 CHAR),
  PER_NAME_FSCL_QTR          VARCHAR2(50 CHAR),
  PER_NAME_FSCL_TER          VARCHAR2(50 CHAR),
  PER_NAME_FSCL_WEEK         VARCHAR2(50 CHAR),
  PER_NAME_FSCL_YEAR         VARCHAR2(50 CHAR),
  PER_NAME_HALF              VARCHAR2(50 CHAR),
  PER_NAME_MONTH             VARCHAR2(50 CHAR),
  PER_NAME_QTR               VARCHAR2(50 CHAR),
  PER_NAME_TER               VARCHAR2(50 CHAR),
  PER_NAME_WEEK              VARCHAR2(50 CHAR),
  PER_NAME_YEAR              VARCHAR2(50 CHAR),
  QUARTER_AGO_DT             DATE,
  QUARTER_AGO_KEY            NUMBER(10),
  QUARTER_AGO_WID            NUMBER(10),
  TRIMESTER_AGO_DT           DATE,
  TRIMESTER_AGO_KEY          NUMBER(10),
  TRIMESTER_AGO_WID          NUMBER(10),
  WEEK_AGO_DT                DATE,
  WEEK_AGO_KEY               NUMBER(10),
  WEEK_AGO_WID               NUMBER(10),
  YEAR_AGO_DT                DATE,
  YEAR_AGO_KEY               NUMBER(10),
  YEAR_AGO_WID               NUMBER(10),
  M_END_CAL_DT_WID           NUMBER(10),
  M_STRT_CAL_DT_WID          NUMBER(10),
  CAL_WEEK_END_DT_WID        NUMBER(10),
  CAL_WEEK_START_DT_WID      NUMBER(10),
  CAL_QTR_END_DT_WID         NUMBER(10),
  CAL_QTR_START_DT_WID       NUMBER(10),
  CAL_YEAR_END_DT_WID        NUMBER(10),
  CAL_YEAR_START_DT_WID      NUMBER(10),
  FST_DAY_CAL_WK_FLG         CHAR(1 CHAR),
  LAST_DAY_CAL_WK_FLG        CHAR(1 CHAR),
  FST_DAY_CAL_MNTH_FLG       CHAR(1 CHAR),
  LAST_DAY_CAL_MNTH_FLG      CHAR(1 CHAR),
  FST_DAY_CAL_QTR_FLG        CHAR(1 CHAR),
  LAST_DAY_CAL_QTR_FLG       CHAR(1 CHAR),
  FST_DAY_CAL_YEAR_FLG       CHAR(1 CHAR),
  LAST_DAY_CAL_YEAR_FLG      CHAR(1 CHAR),
  FSCL_WEEK_START_DT         DATE,
  FSCL_WEEK_END_DT           DATE,
  FSCL_MNTH_START_DT         DATE,
  FSCL_MNTH_END_DT           DATE,
  FSCL_QTR_START_DT          DATE,
  FSCL_QTR_END_DT            DATE,
  FSCL_YEAR_START_DT         DATE,
  FSCL_YEAR_END_DT           DATE,
  FSCL_WEEK_START_DT_WID     NUMBER(10),
  FSCL_WEEK_END_DT_WID       NUMBER(10),
  FSCL_MONTH_START_DT_WID    NUMBER(10),
  FSCL_MONTH_END_DT_WID      NUMBER(10),
  FSCL_QTR_START_DT_WID      NUMBER(10),
  FSCL_QTR_END_DT_WID        NUMBER(10),
  FSCL_YEAR_START_DT_WID     NUMBER(10),
  FSCL_YEAR_END_DT_WID       NUMBER(10),
  FSCL_DIM_QTR_NUM           NUMBER(3),
  FSCL_DIM_MONTH_NUM         NUMBER(5),
  FSCL_MONTH_WEEK_NUM        NUMBER(1),
  FSCL_DIM_WEEK_NUM          NUMBER(6),
  FSCL_DIM_YEAR_NUM          NUMBER(4),
  W_CURRENT_CAL_DAY_CODE     VARCHAR2(50 CHAR),
  W_CURRENT_CAL_WEEK_CODE    VARCHAR2(50 CHAR),
  W_CURRENT_CAL_MONTH_CODE   VARCHAR2(50 CHAR),
  W_CURRENT_CAL_QTR_CODE     VARCHAR2(50 CHAR),
  W_CURRENT_CAL_YEAR_CODE    VARCHAR2(50 CHAR),
  W_CURRENT_FSCL_WEEK_CODE   VARCHAR2(50 CHAR),
  W_CURRENT_FSCL_MONTH_CODE  VARCHAR2(50 CHAR),
  W_CURRENT_FSCL_QTR_CODE    VARCHAR2(50 CHAR),
  W_CURRENT_FSCL_YEAR_CODE   VARCHAR2(50 CHAR),
  FST_DAY_FSCL_WEEK_FLG      CHAR(1 CHAR),
  LAST_DAY_FSCL_WEEK_FLG     CHAR(1 CHAR),
  FST_DAY_FSCL_MNTH_FLG      CHAR(1 CHAR),
  LAST_DAY_FSCL_MNTH_FLG     CHAR(1 CHAR),
  FST_DAY_FSCL_QTR_FLG       CHAR(1 CHAR),
  LAST_DAY_FSCL_QTR_FLG      CHAR(1 CHAR),
  FST_DAY_FSCL_YEAR_FLG      CHAR(1 CHAR),
  LAST_DAY_FSCL_YEAR_FLG     CHAR(1 CHAR),
  W_INSERT_DT                DATE,
  W_UPDATE_DT                DATE,
  TENANT_ID                  VARCHAR2(80 CHAR),
  X_CUSTOM                   VARCHAR2(10 CHAR)
);

Data Seeding Script for Date Dimension 

Note :- Choose start date and end date to populate the date dimension. 

CREATE OR REPLACE PACKAGE W_DAY_D_DATASEED
AS
P_ST_DT DATE := null;
P_ED_DT DATE := null;
PROCEDURE W_DAY_D_PROC;
PROCEDURE BI_TIME_DRIVER;
END W_DAY_D_DATASEED;
/
sho err

CREATE OR REPLACE PACKAGE BODY W_DAY_D_DATASEED
AS
PROCEDURE BI_TIME_DRIVER
as
v_stdt date:='01/JAN/1995';
v_eddt date:='31/DEC/2050';BEGIN
P_ST_DT := v_stdt;
P_ED_DT := v_eddt;
W_DAY_D_PROC;      
END BI_TIME_DRIVER;
PROCEDURE W_DAY_D_PROC
IS
V_ST_DT DATE := P_ST_DT;
V_ED_DT DATE := P_ED_DT;
BEGIN
WHILE V_ED_DT >= V_ST_DT LOOP
INSERT INTO W_DAY_D (ROW_WID ,
CALENDAR_DATE                 ,
CAL_HALF                      ,
CAL_MONTH                     ,
CAL_QTR                       ,
CAL_TRIMESTER                 ,
CAL_WEEK                      ,
CAL_YEAR                      ,
DATASOURCE_NUM_ID             ,
DATE_KEY                      ,
DAY_AGO_DT                    ,
DAY_AGO_KEY                   ,
DAY_AGO_WID                   ,
DAY_DT                        ,
DAY_NAME                      ,
DAY_OF_MONTH                  ,
DAY_OF_WEEK                   ,
DAY_OF_YEAR                   ,
FSCL_DAY_OF_MONTH             ,
FSCL_DAY_OF_WEEK              ,
FSCL_DAY_OF_YEAR              ,
FSCL_HALF                     ,
FSCL_MONTH                    ,
FSCL_QTR                      ,
FSCL_TRIMESTER                ,
FSCL_WEEK                     ,
FSCL_YEAR                     ,
FSCL_FST_DAY_KEY              ,
HALF_AGO_DT                   ,
HALF_AGO_KEY                  ,
HALF_AGO_WID                  ,
INTEGRATION_ID                ,
JULIAN_DAY_NUM                ,
JULIAN_MONTH_NUM              ,
JULIAN_QTR_NUM                ,
JULIAN_TER_NUM                ,
JULIAN_WEEK_NUM               ,
JULIAN_YEAR_NUM               ,
MONTH_AGO_DT                  ,
MONTH_AGO_KEY                 ,
MONTH_AGO_WID                 ,
MONTH_NAME                    ,
PERIOD_KEY                    ,
PER_NAME_FSCL_HALF            ,
PER_NAME_FSCL_MNTH            ,
PER_NAME_FSCL_QTR             ,
PER_NAME_FSCL_TER             ,
PER_NAME_FSCL_WEEK            ,
PER_NAME_FSCL_YEAR            ,
PER_NAME_HALF                 ,
PER_NAME_MONTH                ,
PER_NAME_QTR                  ,
PER_NAME_TER                  ,
PER_NAME_WEEK                 ,
PER_NAME_YEAR                 ,
QUARTER_AGO_DT                ,
QUARTER_AGO_KEY               ,
QUARTER_AGO_WID               ,
TRIMESTER_AGO_DT              ,
TRIMESTER_AGO_KEY             ,
TRIMESTER_AGO_WID             ,
WEEK_AGO_DT                   ,
WEEK_AGO_KEY                  ,
WEEK_AGO_WID                  ,
YEAR_AGO_DT                   ,
YEAR_AGO_KEY                  ,
YEAR_AGO_WID                  ,
M_END_CAL_DT_WID              ,
M_STRT_CAL_DT_WID             ,
CAL_WEEK_END_DT_WID           ,
CAL_WEEK_START_DT_WID         ,
CAL_QTR_END_DT_WID            ,
CAL_QTR_START_DT_WID          ,
CAL_YEAR_END_DT_WID           ,
CAL_YEAR_START_DT_WID         ,
FST_DAY_CAL_WK_FLG            ,
LAST_DAY_CAL_WK_FLG           ,
FST_DAY_CAL_MNTH_FLG          ,
LAST_DAY_CAL_MNTH_FLG         ,
FST_DAY_CAL_QTR_FLG           ,
LAST_DAY_CAL_QTR_FLG          ,
FST_DAY_CAL_YEAR_FLG          ,
LAST_DAY_CAL_YEAR_FLG         ,
FSCL_WEEK_START_DT            ,
FSCL_WEEK_END_DT              ,
FSCL_MNTH_START_DT            ,
FSCL_MNTH_END_DT              ,
FSCL_QTR_START_DT             ,
FSCL_QTR_END_DT               ,
FSCL_YEAR_START_DT            ,
FSCL_YEAR_END_DT              ,
FSCL_WEEK_START_DT_WID        ,
FSCL_WEEK_END_DT_WID          ,
FSCL_MONTH_START_DT_WID       ,
FSCL_MONTH_END_DT_WID         ,
FSCL_QTR_START_DT_WID         ,
FSCL_QTR_END_DT_WID           ,
FSCL_YEAR_START_DT_WID        ,
FSCL_YEAR_END_DT_WID          ,
FSCL_DIM_QTR_NUM              ,
FSCL_DIM_MONTH_NUM            ,
FSCL_MONTH_WEEK_NUM           ,
FSCL_DIM_WEEK_NUM             ,
FSCL_DIM_YEAR_NUM             ,
W_CURRENT_CAL_DAY_CODE        ,
W_CURRENT_CAL_WEEK_CODE       ,
W_CURRENT_CAL_MONTH_CODE      ,
W_CURRENT_CAL_QTR_CODE        ,
W_CURRENT_CAL_YEAR_CODE       ,
W_CURRENT_FSCL_WEEK_CODE      ,
W_CURRENT_FSCL_MONTH_CODE     ,
W_CURRENT_FSCL_QTR_CODE       ,
W_CURRENT_FSCL_YEAR_CODE      ,
FST_DAY_FSCL_WEEK_FLG         ,
LAST_DAY_FSCL_WEEK_FLG        ,
FST_DAY_FSCL_MNTH_FLG         ,
LAST_DAY_FSCL_MNTH_FLG        ,
FST_DAY_FSCL_QTR_FLG          ,
LAST_DAY_FSCL_QTR_FLG         ,
FST_DAY_FSCL_YEAR_FLG         ,
LAST_DAY_FSCL_YEAR_FLG        ,
W_INSERT_DT                   ,
W_UPDATE_DT                   ,
TENANT_ID                     ,
X_CUSTOM                      ) VALUES
(
TO_NUMBER(TO_CHAR(V_ST_DT,'YYYYMMDD'))   ,
V_ST_DT ,
CASE WHEN TO_NUMBER(TO_CHAR(V_ST_DT, 'MM'))>6 THEN 2 ELSE 1 END ,
TO_NUMBER(TO_CHAR(V_ST_DT, 'MM')) ,
CASE WHEN TO_NUMBER(TO_CHAR(V_ST_DT, 'MM'))<=3 THEN 1 WHEN TO_NUMBER(TO_CHAR(V_ST_DT, 'MM'))>3 AND
TO_NUMBER(TO_CHAR(V_ST_DT, 'MM'))<=6 THEN 2 WHEN TO_NUMBER(TO_CHAR(V_ST_DT, 'MM'))>6 AND
TO_NUMBER(TO_CHAR(V_ST_DT, 'MM'))<=9 THEN 3 ELSE 4 END ,
CASE WHEN TO_NUMBER(TO_CHAR(V_ST_DT, 'MM'))<=4 THEN 1 WHEN TO_NUMBER(TO_CHAR(V_ST_DT, 'MM'))<=8 AND
TO_NUMBER(TO_CHAR(V_ST_DT, 'MM'))>4 THEN 2 ELSE 3 END ,
TO_NUMBER(TO_CHAR(V_ST_DT, 'WW')) ,
TO_NUMBER(TO_CHAR(V_ST_DT, 'YYYY')) ,
0 ,
TO_NUMBER(TO_CHAR(V_ST_DT,'J')) ,
V_ST_DT-1 ,
TO_NUMBER(TO_CHAR(V_ST_DT-1,'J')),
TO_NUMBER(TO_CHAR(V_ST_DT-1,'YYYYMMDD')),
V_ST_DT ,
TO_CHAR(V_ST_DT,'DAY'),
TO_NUMBER(TO_CHAR(V_ST_DT,'DD')),
TO_NUMBER(TO_CHAR(V_ST_DT,'D')),
TO_NUMBER(TO_CHAR(V_ST_DT,'DDD')),
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
TO_NUMBER(TO_CHAR(V_ST_DT,'YYYYMMDD')) ,
TO_NUMBER(TO_CHAR(V_ST_DT,'J')),
((TO_NUMBER(TO_CHAR(V_ST_DT,'YYYY'))+ 4713) * 12)  + TO_NUMBER(TO_CHAR(V_ST_DT,'MM')),
((TO_NUMBER(TO_CHAR(TO_DATE(TO_NUMBER(TO_CHAR(V_ST_DT,'YYYYMM')),'YYYYMM'),'YYYY'))+ 4713) * 4)  +
CASE WHEN TO_NUMBER(TO_CHAR(TO_DATE(TO_NUMBER(TO_CHAR(V_ST_DT,'YYYYMM')),'YYYYMM'),'MM'))<=3
THEN 1 WHEN TO_NUMBER(TO_CHAR(TO_DATE(TO_NUMBER(TO_CHAR(V_ST_DT,'YYYYMM')),'YYYYMM'),'MM'))>3
AND TO_NUMBER(TO_CHAR(TO_DATE(TO_NUMBER(TO_CHAR(V_ST_DT,'YYYYMM')),'YYYYMM'),'MM'))<=6
THEN 2 WHEN TO_NUMBER(TO_CHAR(TO_DATE(TO_NUMBER(TO_CHAR(V_ST_DT,'YYYYMM')),'YYYYMM'),'MM'))>6
AND TO_NUMBER(TO_CHAR(TO_DATE(TO_NUMBER(TO_CHAR(V_ST_DT,'YYYYMM')),'YYYYMM'),'MM'))<=9 THEN 3 ELSE 4 END ,
((TO_NUMBER(TO_CHAR(TO_DATE(TO_NUMBER(TO_CHAR(V_ST_DT,'YYYYMM')),'YYYYMM'),'YYYY'))+ 4713) * 3)  +
CASE WHEN TO_NUMBER(TO_CHAR(TO_DATE(TO_NUMBER(TO_CHAR(V_ST_DT,'YYYYMM')),'YYYYMM'),'MM'))<=4
THEN 1 WHEN TO_NUMBER(TO_CHAR(TO_DATE(TO_NUMBER(TO_CHAR(V_ST_DT,'YYYYMM')),'YYYYMM'),'MM'))>4
AND TO_NUMBER(TO_CHAR(TO_DATE(TO_NUMBER(TO_CHAR(V_ST_DT,'YYYYMM')),'YYYYMM'),'MM'))<=8
THEN 2 ELSE 3 END ,
Null,
TO_NUMBER(TO_CHAR(V_ST_DT,'YYYY'))+ 4713 ,
ADD_MONTHS(V_ST_DT,-1) ,
TO_NUMBER(TO_CHAR(ADD_MONTHS(V_ST_DT,-1),'J')) ,
TO_NUMBER(TO_CHAR(ADD_MONTHS(V_ST_DT,-1),'YYYYMMDD')) ,
TO_CHAR(V_ST_DT,'MONTH'),
TO_NUMBER(TO_CHAR(V_ST_DT,'YYYYMMDD')),
NULL ,
NULL ,
NULL ,
NULL ,
NULL ,
NULL ,
TO_CHAR(V_ST_DT,'YYYY')||' HALF'||TO_CHAR(CASE WHEN TO_NUMBER(TO_CHAR(V_ST_DT,'MM')) <6 THEN 1 ELSE 2
END),
TO_CHAR(V_ST_DT, 'YYYY / MM'),
TO_CHAR(V_ST_DT,'YYYY')||' Q '||TO_CHAR(V_ST_DT,  'Q'),
TO_CHAR(V_ST_DT,'YYYY')|| 'T' ||TO_CHAR(CASE WHEN TO_NUMBER(TO_CHAR(V_ST_DT, 'MM'))<=4 THEN 1 WHEN
TO_NUMBER(TO_CHAR(V_ST_DT, 'MM'))<=8 AND TO_NUMBER(TO_CHAR(V_ST_DT, 'MM'))>4 THEN 2 ELSE 3 END) ,
TO_CHAR(V_ST_DT,'YYYY')|| ' WEEK' ||  TO_NUMBER(TO_CHAR(V_ST_DT, 'WW')),
TO_NUMBER(TO_CHAR(V_ST_DT, 'YYYY')),
CASE WHEN TO_NUMBER(TO_CHAR(V_ST_DT,'Q'))<=3 THEN ADD_MONTHS(V_ST_DT,-3) WHEN
TO_NUMBER(TO_CHAR(V_ST_DT,'Q'))>3 AND TO_NUMBER(TO_CHAR(V_ST_DT,'Q'))<=6 THEN ADD_MONTHS(V_ST_DT,-3) WHEN
TO_NUMBER(TO_CHAR(V_ST_DT,'Q'))>6 AND TO_NUMBER(TO_CHAR(V_ST_DT,'Q'))<=9 THEN  ADD_MONTHS(V_ST_DT,-3) ELSE
ADD_MONTHS(V_ST_DT,-3)  END ,
NULL ,
NULL ,
CASE WHEN TO_NUMBER(TO_CHAR(V_ST_DT,'Q'))<=4 THEN ADD_MONTHS(V_ST_DT,-4) WHEN
TO_NUMBER(TO_CHAR(V_ST_DT,'Q'))>4 AND TO_NUMBER(TO_CHAR(V_ST_DT,'Q'))<=8 THEN ADD_MONTHS(V_ST_DT,-4)  
ELSE ADD_MONTHS(V_ST_DT,-4)  END ,
NULL ,
NULL ,
V_ST_DT-7 ,
TO_NUMBER(TO_CHAR(V_ST_DT-7,'J')) ,
TO_NUMBER(TO_CHAR(V_ST_DT-7,'YYYYMMDD')) ,
ADD_MONTHS(V_ST_DT,-12) ,
TO_NUMBER(TO_CHAR(ADD_MONTHS(V_ST_DT,-12),'J')) ,
TO_NUMBER(TO_CHAR(ADD_MONTHS(V_ST_DT,-12),'YYYYMMDD')) ,
TO_NUMBER(TO_CHAR(LAST_DAY(V_ST_DT),'YYYYMMDD')),
TO_NUMBER(TO_CHAR(V_ST_DT,'YYYYMM'))||'01' ,
NULL ,
NULL ,
NULL ,
NULL ,
TO_NUMBER(TO_CHAR(V_ST_DT,'YYYY'))||'1231' ,
TO_NUMBER(TO_CHAR(V_ST_DT,'YYYY'))||'0101'  ,
CASE WHEN TO_NUMBER(TO_CHAR(V_ST_DT,'D')) = 1 THEN 'Y'ELSE 'N'  END ,
CASE WHEN TO_NUMBER(TO_CHAR(V_ST_DT,'D')) = 7 THEN 'Y'ELSE 'N'  END ,
CASE WHEN TO_NUMBER(TO_CHAR(V_ST_DT,'DD')) = 1 THEN 'Y' ELSE 'N' END,
CASE WHEN TO_NUMBER(TO_CHAR(V_ST_DT,'DD')) = TO_NUMBER(TO_CHAR(LAST_DAY(V_ST_DT),'DD')) THEN 'Y' ELSE 'N'
END,
CASE WHEN TO_NUMBER(TO_CHAR(V_ST_DT,'DD'))=1 THEN CASE WHEN TO_NUMBER(TO_CHAR(V_ST_DT,'MM'))=1 OR
TO_NUMBER(TO_CHAR(V_ST_DT,'MM'))=4 OR TO_NUMBER(TO_CHAR(V_ST_DT,'MM'))=7 OR
TO_NUMBER(TO_CHAR(V_ST_DT,'MM'))=10 THEN 'Y'ELSE 'N' END ELSE 'N' END  ,
CASE WHEN CASE WHEN TO_NUMBER(TO_CHAR(V_ST_DT,'DD')) = TO_NUMBER(TO_CHAR(LAST_DAY(V_ST_DT),'DD')) THEN 'Y'
ELSE 'N' END  = 'Y' THEN CASE WHEN TO_NUMBER(TO_CHAR(V_ST_DT,'MM'))=3 OR
TO_NUMBER(TO_CHAR(V_ST_DT,'MM'))=6 OR TO_NUMBER(TO_CHAR(V_ST_DT,'MM'))=9 OR
TO_NUMBER(TO_CHAR(V_ST_DT,'MM'))=12 THEN 'Y' ELSE 'N' END ELSE 'N' END,
CASE WHEN TO_NUMBER(TO_CHAR(V_ST_DT,'DDD'))=1 THEN 'Y' ELSE 'N' END ,
CASE WHEN TO_NUMBER(TO_CHAR(V_ST_DT,'MM'))=12 AND TO_NUMBER(TO_CHAR(V_ST_DT,'DD'))=31 THEN 'Y' ELSE 'N'
END ,
NULL  ,
NULL  ,
NULL  ,
NULL  ,
NULL  ,
NULL  ,
NULL  ,
NULL  ,
NULL  ,
NULL  ,
NULL  ,
NULL  ,
NULL  ,
NULL  ,
NULL  ,
NULL  ,
NULL  ,
NULL  ,
NULL  ,
NULL  ,
NULL  ,
NULL  ,
NULL  ,
NULL  ,
NULL  ,
NULL  ,
NULL  ,
NULL  ,
NULL  ,
NULL  ,
NULL  ,
NULL  ,
NULL  ,
NULL  ,
NULL  ,
NULL  ,
NULL  ,
NULL  ,
SYSDATE              ,
SYSDATE              ,
NULL                   ,
NULL       )                ;
V_ST_DT := V_ST_DT + 1;
END LOOP;

UPDATE W_DAY_D A
SET
QUARTER_AGO_KEY = (SELECT TO_NUMBER(TO_CHAR(QUARTER_AGO_DT,'J')) FROM W_DAY_D A1 WHERE A.DATE_KEY = A1.DATE_KEY),
QUARTER_AGO_WID = (SELECT TO_NUMBER(TO_CHAR(QUARTER_AGO_DT,'YYYYMMDD')) FROM W_DAY_D B1 WHERE A.DATE_KEY = B1.DATE_KEY),
TRIMESTER_AGO_KEY =(SELECT TO_NUMBER(TO_CHAR(TRIMESTER_AGO_DT,'J')) FROM W_DAY_D C1 WHERE A.DATE_KEY = C1.DATE_KEY),
TRIMESTER_AGO_WID =(SELECT TO_NUMBER(TO_CHAR(TRIMESTER_AGO_DT,'YYYYMMDD')) FROM W_DAY_D D1 WHERE A.DATE_KEY = D1.DATE_KEY),
CAL_QTR_START_DT_WID = 10000*CAL_YEAR+100*3*CAL_QTR+1,
CAL_QTR_END_DT_WID = CASE  WHEN CAL_QTR = 1 OR CAL_QTR = 4 THEN 10000*CAL_YEAR+100*3*CAL_QTR+31 ELSE 10000*CAL_YEAR+100*3*CAL_QTR+30 END;
COMMIT;
Exception
 when others then
  dbms_output.put_line ('Exception ' || sqlerrm);
END  W_DAY_D_PROC;
END  W_DAY_D_DATASEED;

/
sho err
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)