I need to create a lookup table of calendar for 10 years starting from 2020-01-01, but keep getting the error from the title. Could anyone tell me how to fix the syntax?
I saw there are similar questions but I still don't know how to fix it...
Thank you in advance.
CREATE TABLE LU_DATE
(
YMD_DATE DATE NOT NULL,
YMD_ID NUMBER(8,0) NOT NULL,
YW_ID VARCHAR(7 char) NOT NULL,
YQ_ID VARCHAR(6 char) NOT NULL,
YM_ID NUMBER(6,0) NOT NULL,
Y_ID NUMBER(4,0) NOT NULL,
YMLY_ID NUMBER(6,0) NOT NULL
)
;
CREATE INDEX LU_DATEIAX ON LU_DATE
(
YMD_DATE ASC
)
;
CREATE INDEX LU_DATEIBX ON LU_DATE
(
YMD_ID ASC
)
;
INSERT INTO LU_DATE
(
YMD_DATE,
YMD_ID,
YW_ID,
YQ_ID,
YM_ID,
Y_ID,
YMLY_ID
)
SELECT
TO_DATE('20200101','YYYYMMDD') rownum -1 AS YMD_DATE,
TO_NUMBER(TO_CHAR(TO_DATE('20200101') rownum -1,'YYYYMMDD')) AS YMD_ID,
TO_NUMBER(TO_CHAR(TO_DATE('20200101') rownum -1,'YYYY'))*100 TO_NUMBER(TO_CHAR(TO_DATE('20200101') rownum -1,'YYYYMMDD'),'ww') AS YW_ID,
TO_NUMBER(TO_CHAR(TO_DATE('20200101') rownum -1,'YYYY'))*10 TO_NUMBER(TO_CHAR(TO_DATE('20200101') rownum -1,'YYYYMMDD'),'q') AS YQ_ID,
TO_NUMBER(TO_CHAR(TO_DATE('20200101') rownum -1,'YYYYMM')) AS YM_ID,
TO_NUMBER(TO_CHAR(TO_DATE('20200101') rownum -1,'YYYY')) AS Y_ID,
TO_NUMBER(TO_CHAR(ADD_MONTHS(TO_DATE('20200101') rownum -1 ,-12),'YYYYMM')) AS YMLY_ID
FROM DUAL
connect by TO_DATE('20200101') rownum -1 <= TO_DATE('20301231')
;
CodePudding user response:
Use virtual columns (then you will never have a situation where your values get out-of-sync and you have two different date values in the same row):
CREATE TABLE LU_DATE
(
YMD_DATE DATE
NOT NULL,
YMD_ID NUMBER(8,0)
GENERATED ALWAYS AS (TO_NUMBER(TO_CHAR(ymd_date, 'YYYYMMDD')))
NOT NULL,
YW_ID VARCHAR2(6 char)
GENERATED ALWAYS AS (TO_CHAR(ymd_date, 'YYYYWW'))
NOT NULL,
YQ_ID VARCHAR2(5 char)
GENERATED ALWAYS AS (TO_CHAR(ymd_date, 'YYYYQ'))
NOT NULL,
YM_ID NUMBER(6,0)
GENERATED ALWAYS AS (TO_NUMBER(TO_CHAR(ymd_date, 'YYYYMM')))
NOT NULL,
Y_ID NUMBER(4,0)
GENERATED ALWAYS AS (EXTRACT(YEAR FROM ymd_date))
NOT NULL,
YMLY_ID NUMBER(6,0)
GENERATED ALWAYS AS (TO_NUMBER(TO_CHAR(ADD_MONTHS(ymd_date, -12), 'YYYYMM')))
NOT NULL
);
Then use a DATE
literal:
INSERT INTO LU_DATE(YMD_DATE)
SELECT DATE '2020-01-01' LEVEL -1
FROM DUAL
connect by DATE '2020-01-01' LEVEL -1 < DATE '2031-01-01';