当创建一个带有左外部联接和最大值的视图时，我如何解决ORA-01799？

Question

我正在尝试创建一个连接几个表的视图，其中一个连接使用的是最大值，我认为这会在我们的Oracle DB中生成以下错误: ORA-01799:列可能没有外连接到子查询。是否有人可以帮助我更改状态以创建视图？这个查询运行得很好，只有当我尝试用它创建一个视图时才会出现问题。

CREATE or replace VIEW xxxxxx_V (ID, SERVICE_ID, PARENT_ID, PARENT_MATERIALITY, PARENT_END_STATUS,
PARENT_OUTS_TYPE, END_STATUS, MATERIALITY, OUTS_TYPE, CREATED_BY,
SERVICE_DESCRIPTION_SHORT, VERSION, OUTSOURCER_ID, OUTSOURCER_NAME, INSOURCER_ID, INSOURCER_NAME, PLANNED_SERVICE_START) AS
select c.id id, s.id service_id, s.outsourcing_id parent_id, p.materiality parent_materiality, p.end_status parent_end_status,
p.outsourcing_type parent_outs_type, c.end_status end_status, c.materiality materiality, c.outsourcing_type outs_type, c.created_by created_by,
r.service_description_short, r.version, r.outsourcer outsourcer_id, o.name outsourcer_name , r.insourcer insourcer_id,i.name insourcer_name, r.planned_service_start
from OS_OUTSOURCING_CONTRACT c
left join OS_SERVICES s on s.id = c.service_id
left join OS_OUTSOURCING_CONTRACT p on p.id = s.outsourcing_id
left join OS_RISK_ASSESSMENT r on r.outsourcing_id = c.id and r.version = (select max(version) from OS_RISK_ASSESSMENT where outsourcing_id = c.id)
left join OS_COMPANY_INSOURCER i on i.id = r.insourcer
left join OS_COMPANY_OUTSOURCER o on o.id = r.outsourcer

Answer 1

将子查询联接到查询中：

CREATE or replace VIEW xxxxxx_V
   (ID, SERVICE_ID, PARENT_ID, PARENT_MATERIALITY, PARENT_END_STATUS,
    PARENT_OUTS_TYPE, END_STATUS, MATERIALITY, OUTS_TYPE, CREATED_BY,
    SERVICE_DESCRIPTION_SHORT, VERSION, OUTSOURCER_ID, OUTSOURCER_NAME,
    INSOURCER_ID, INSOURCER_NAME, PLANNED_SERVICE_START) AS
select c.id,
       s.id service_id,
       s.outsourcing_id parent_id,
       p.materiality parent_materiality,
       p.end_status parent_end_status,
       p.outsourcing_type parent_outs_type,
       c.end_status,
       c.materiality,
       c.outsourcing_type outs_type,
       c.created_by,
       r.service_description_short,
       r.version,
       r.outsourcer outsourcer_id,
       o.name outsourcer_name,
       r.insourcer insourcer_id,
       i.name insourcer_name,
       r.planned_service_start
from OS_OUTSOURCING_CONTRACT c
LEFT OUTER JOIN (select outsourcing_id,
                        max(version) AS MAX_VERSION
                   from OS_RISK_ASSESSMENT
                   group by outsourcing_id) ora
  ON ora.OUTSOURCING_ID = c.id
left join OS_SERVICES s
  on s.id = c.service_id
left join OS_OUTSOURCING_CONTRACT p
  on p.id = s.outsourcing_id
left join OS_RISK_ASSESSMENT r
  on r.outsourcing_id = ora.OUTSOURCING_ID and
     r.version = ora.MAX_VERSION
left join OS_COMPANY_INSOURCER i
  on i.id = r.insourcer
left join OS_COMPANY_OUTSOURCER o
  on o.id = r.outsourcer