Hi, I am trying to put a large query into a materilized view VW_BANK_MASTER_C as there will be frequent DML's done on the table BANK_MASTER_C as shown below:
CREATE MATERIALIZED VIEW VW_BANK_MASTER_C
REFRESH FORCE ON DEMAND
AS
((SELECT DISTINCT BANK_CODE,
KANA_FULL_NAME,
KANA_SHORT_NAME,
'Y',
TO_DATE('01-JAN-1900', 'DD/MON/YYYY'),
TO_DATE(DELETE_DATE,'YYYYMMDD'),
GLOBAL.COUNTRY_OFFICE GLOBAL_CNTRY_OFFICE1,
GLOBAL.COUNTRY_CODE GLOBAL_CNTRY_CODE1,
'SYSTEM',
GLOBAL.APPLICATION_DATE GLOBAL_APP_DATE1,
'SYSTEM',
GLOBAL.APPLICATION_DATE GLOBAL_APP_DATE2,
1,
'O',
'A',
'Y',
KANA_SHORT_NAME,
KANJI_SHORT_NAME
FROM BANK_MASTER_C
WHERE TO_DATE(DELETE_DATE,'YYYYMMDD') >= GLOBAL.APPLICATION_DATE
AND BANK_STATUS IN ('05','07','02','09'))
UNION
(SELECT DISTINCT BANK_CODE,
KANA_FULL_NAME,
KANA_SHORT_NAME,
'Y',
TO_DATE('01-JAN-1900', 'DD/MON/YYYY'),
TO_DATE(DELETE_DATE,'YYYYMMDD'),
GLOBAL.COUNTRY_OFFICE GLOBAL_CNTRY_OFFICE2,
GLOBAL.COUNTRY_CODE GLOBAL_CNTRY_CODE2,
'SYSTEM',
GLOBAL.APPLICATION_DATE GLOBAL_APP_DATE3,
'SYSTEM',
GLOBAL.APPLICATION_DATE GLOBAL_APP_DATE4,
1,
'O',
'A',
'Y',
KANA_SHORT_NAME,
KANJI_SHORT_NAME
FROM BANK_MASTER_C
WHERE DELETE_DATE IS NULL
AND BANK_STATUS IN ('00','01','04','08')))
I am facing a duplicate column error even after aliasing the columns GLOBAL.COUNTRY_OFFICE, GLOBAL.COUNTRY_CODE and GLOBAL.APPLICATION_DATE in the two union'ed queries. GLOBAL is a global package which will return a string for COUNTRY CODE and COUNTRY OFFICE. Where as APPLICATION DATE will return DATE. What is wrong with this code. Any help would be greatly appreciated.