Oracle执行create view语句时提示ORA-00998:必须使用列别名命名此表达式

Question

create view VW_PUB_SYS_DICT_CACHE as
  (
    SELECT rownum,
           0,
           a."'CACHESYSDICT'",
           a."COMBOBOX_CODE",
           a."OPTION_VALUE",
           a."OPTION_NAME"
    FROM (
           SELECT 'cacheSysDict',
                  COMBOBOX_CODE,
                  OPTION_VALUE,
                  OPTION_NAME
           FROM TB_PUB_DATADICT
           UNION ALL
           SELECT 'cacheBranch',
                  BRH_CODE,
                  BRH_CODE,
                  BRH_SHT_NAME
           FROM TB_PUB_BRANCH
           UNION ALL
           SELECT 'cacheSysDict',
                  'userCode',
                  AUTH_UID,
                  TELLER_NAME
           FROM TB_PUB_USER
           WHERE AUTH_TYPE = 'WB'
         ) a
  );

尝试过给所有表加别名，也无效。

我单独执行查询语句是可以的：

SELECT rownum,
           0,
           a."'CACHESYSDICT'",
           a."COMBOBOX_CODE",
           a."OPTION_VALUE",
           a."OPTION_NAME"
    FROM (
           SELECT 'cacheSysDict',
                  COMBOBOX_CODE,
                  OPTION_VALUE,
                  OPTION_NAME
           FROM TB_PUB_DATADICT
           UNION ALL
           SELECT 'cacheBranch',
                  BRH_CODE,
                  BRH_CODE,
                  BRH_SHT_NAME
           FROM TB_PUB_BRANCH
           UNION ALL
           SELECT 'cacheSysDict',
                  'userCode',
                  AUTH_UID,
                  TELLER_NAME
           FROM TB_PUB_USER
           WHERE AUTH_TYPE = 'WB'
         ) a

Answer 1

自己解决了，前面加上视图的字段名即可。

CREATE OR REPLACE VIEW VW_PUB_SYS_DICT_CACHE ("ID", "OPTLOCK", "NAME", "TAG", "KEY", "VALUE") AS (
  SELECT rownum,
         0,
         a."'CACHESYSDICT'",
         a."COMBOBOX_CODE",
         a."OPTION_VALUE",
         a."OPTION_NAME"
  FROM (
         SELECT 'cacheSysDict',
                COMBOBOX_CODE,
                OPTION_VALUE,
                OPTION_NAME
         FROM TB_PUB_DATADICT
         UNION ALL
         SELECT 'cacheBranch',
                BRH_CODE,
                BRH_CODE,
                BRH_SHT_NAME
         FROM TB_PUB_BRANCH
         UNION ALL
         SELECT 'cacheSysDict',
                'userCode',
                AUTH_UID,
                TELLER_NAME
         FROM TB_PUB_USER
         WHERE AUTH_TYPE = 'WB') a
);