qq997404392
伪墨
采纳率61.8%
2019-02-22 10:30 阅读 7.4k

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

5
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
  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 复制链接分享

3条回答 默认 最新

  • 已采纳
    qq997404392 伪墨 2019-02-22 17:26

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

    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
    );
    
    点赞 1 评论 复制链接分享
  • u012196300 zhaoops 2019-02-22 10:32

    列名重复了BRH_CODE,
    而且UNION ALL 的列名也不一致

    点赞 评论 复制链接分享
  • yangxingzou zoyation 2019-02-22 10:48

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

    点赞 评论 复制链接分享

相关推荐