qq_39906279
2019-03-26 09:46
采纳率: 85.7%
浏览 1.1k

oracle创建视图提示缺少右括号

1.创建视图中的select 语句单独执行查询没问题,在外面包上 create or replace view v_视图名 as() 时报ORA-00907:缺失右括号
2.创建视图中的查询语句大体如下

    select 
                    CBS.id,
                    CBS.name,
                    CBS.ST,
                    BBI.id,
                    BBI.update_time,
                    BBI.rcrd_st 
    from CBS left join BBI 
    on CBS.id=BBI.id
    where CBS.ST='1'
    order by BBI.update_time desc   

3:之前没有加上order by条件时可以建视图成功,加上后,内部的select语句单独查询没问题,建视图时报缺少右括号

  • 写回答
  • 好问题 提建议
  • 关注问题
  • 收藏
  • 邀请回答

3条回答 默认 最新

  • qq_39906279 2019-03-26 10:19
    已采纳

    解决了。。。是create view时外面包了括号,我们公司一直这么用,我以为是规范来着,问了朋友的公司,他们 没用括号包起来。把括号去掉就行了

    已采纳该答案
    评论
    解决 无用
    打赏 举报
  • 1进击的小白 2019-03-26 09:55

    select
    CBS.id,
    CBS.name,
    CBS.ST,
    BBI.id,
    BBI.update_time,
    BBI.rcrd_st
    from CBS left join BBI
    on CBS.id=BBI.id
    where CBS.ST='1'
    order by BBI.update_time desc 直接这样不行吗?

    评论
    解决 无用
    打赏 举报
  • 那小麦 2019-03-26 13:56

    with语句貌似也没那种写法
    CREATE OR REPLACE VIEW JC_ZSJGS_V_ZZJGCC as
    WITH B --(FL_XH, FATHER, SON, STDATE, ENDATE, POSITION, SXBZ, TYBZ) --这个注释掉
    AS (SELECT A.FL_XH, trim(to_char(to_number(A.FJYZT_XH))) AS FATHER, trim(to_char(to_number(A.JYZT_XH))) AS SON,
    A.S_TIME AS STDATE, A.E_TIME AS ENDDATE, A.CJ_XH AS POSITION, A.SYZT_BZ AS SXBZ,
    A.SFSC_BZ AS TYBZ FROM JC_ZSJGS_T_ZZJGCC A
    where A.FL_XH = LC_FL_XH
    UNION ALL
    SELECT B.FL_XH, trim(to_char(to_number(C.FATHER))) AS FATHER,
    trim(to_char(to_number(B.SON))) AS SON,
    case when B.STDATE>C.STDATE THEN B.STDATE
    ELSE C.STDATE END AS STDATE,
    CASE WHEN B.ENDATE>C.ENDDATE THEN C.ENDDATE
    ELSE B.ENDATE END AS ENDATE,
    B.POSITION, B.SXBZ, B.TYBZ
    FROM
    (SELECT A.FL_XH, A.FJYZT_XH AS FATHER, A.JYZT_XH AS SON, A.S_TIME AS STDATE,
    A.E_TIME AS ENDDATE, A.CJ_XH AS POSITION, A.SYZT_BZ AS SXBZ, A.SFSC_BZ AS TYBZ
    FROM JC_ZSJGS_T_ZZJGCC A
    WHERE COALESCE(A.FJYZT_XH,0) <> 0 AND A.FL_XH = LC_FL_XH
    ) AS C, B
    WHERE trim(to_char(to_number(C.SON))) = COALESCE(B.FATHER,'0') AND C.FL_XH = B.FL_XH)
    SELECT B.FL_XH,B.FATHER,B.SON,B.STDATE,B.ENDATE,B.POSITION,B.SXBZ,B.TYBZ
    FROM B WHERE STDATE<ENDATE;
    直升机父母”要学会放手www.fenbiedu.cn/index/article/detail/id/1425.html

    评论
    解决 无用
    打赏 举报

相关推荐 更多相似问题