请教关于创建视图的问题

2012-11-15 08:57:53 224 阅读 AI 写同款 · GEO 优化AI 选题数据监测一键分发 ›

oracle物化视图创建由于权限问题导致无法创建成功问题最近遇到sys用户创建其他用户的物化视图时报错，下面记录一下问题及整个处理过程: 通过确认上述语句中的子查询语句执行情况，有返回结果集，说明sys用户访问正常，相关表及dblink访问正常，表是存在的。那么该报错可能是该物化视图owner用户的权限问题。步骤1:授予用户在子查询中涉及的第1个表的select权限: grant select on ucr.td_m_tabto gzuser；步骤2:授予子查询中涉及的第2个dblink的权限: 经过查询该dblink，发现其owner是PU. 阅读详情

有这么一个查询语句如下，我想把这个查询做成一个视图在程序中调用，但是where那部分是我想要传递的查询条件，如果我把这个查询语句做成视图以后，怎么能把查询条件传递进去



with aa as

 (select t.code, t.code_name from code t where t.code_type_id = 12),

bb as

 (select t2.apply_type, t2.hospital_id, sum(cf.fee_amount) fee

    from consult_fee cf

    left join consult_booking t2

      on cf.booking_id = t2.id

   group by t2.apply_type, t2.hospital_id)

select t4.hospital_name, aa.code_name, t.counts, bb.fee, t4.balance

  from (select t2.apply_type, t2.hospital_id, count(t1.id) counts

          from consult_report t1

          left join consult_booking t2

            on t1.consult_booking_id = t2.id

          left join consult_fee t3

            on t2.id = t3.booking_id

        left join doctor_info t4

        on t1.expert_id = t4.id

         where t1.consultation_status in (6, 7)

        and t4.at_hospital_id = 100

        and t2.hospital_id = 1

        and t1.CONSULTATION_TIME between

            to_date('2012-01-01', 'yyyy-mm-dd') and sysdate

         group by t2.apply_type, t2.hospital_id) t

  left join aa

    on t.apply_type = aa.code

  left join bb

    on t.apply_type = bb.apply_type

  left join hospital_info t4

    on t.hospital_id = t4.id;