ORA-00957: duplicate column name

648 views Asked by At

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.

0

There are 0 answers