Oracle创建含LEFT OUTER JOIN与MAX子查询的视图触发ORA-01799的解决

• ORA-01799的触发规则是外连接关联条件不允许直接使用依赖外层查询的相关子查询，Oracle对视图创建场景下的该语法校验比直接执行SELECT更严格，因此会出现单独跑SELECT正常、建视图报错的情况。

将原关联条件中动态计算最大版本的相关子查询，提前通过CTE做全局预聚合，规避外连接条件携带相关子查询的问题，修改后与原查询逻辑完全等价。

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
WITH latest_risk_map AS (
    SELECT 
        outsourcing_id,
        MAX(version) AS max_risk_version
    FROM OS_RISK_ASSESSMENT
    GROUP BY outsourcing_id
)
SELECT 
    c.id, 
    s.id AS service_id, 
    s.outsourcing_id AS parent_id, 
    p.materiality AS parent_materiality, 
    p.end_status AS parent_end_status,
    p.outsourcing_type AS parent_outs_type, 
    c.end_status, 
    c.materiality, 
    c.outsourcing_type AS outs_type, 
    c.created_by,
    r.service_description_short, 
    r.version, 
    r.outsourcer AS outsourcer_id, 
    o.name AS outsourcer_name, 
    r.insourcer AS insourcer_id,
    i.name AS 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 latest_risk_map rm 
    ON rm.outsourcing_id = c.id
LEFT JOIN OS_RISK_ASSESSMENT r 
    ON r.outsourcing_id = rm.outsourcing_id 
    AND r.version = rm.max_risk_version
LEFT JOIN OS_COMPANY_INSOURCER i 
    ON i.id = r.insourcer
LEFT JOIN OS_COMPANY_OUTSOURCER o 
    ON o.id = r.outsourcer

如果需要避免最大版本下多条重复记录导致的视图结果重复，可改用窗口函数过滤，逻辑更灵活：

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
WITH risk_ranked AS (
    SELECT 
        outsourcing_id,
        service_description_short,
        version,
        outsourcer,
        insourcer,
        planned_service_start,
        ROW_NUMBER() OVER (PARTITION BY outsourcing_id ORDER BY version DESC) AS rn
    FROM OS_RISK_ASSESSMENT
)
SELECT 
    c.id, 
    s.id AS service_id, 
    s.outsourcing_id AS parent_id, 
    p.materiality AS parent_materiality, 
    p.end_status AS parent_end_status,
    p.outsourcing_type AS parent_outs_type, 
    c.end_status, 
    c.materiality, 
    c.outsourcing_type AS outs_type, 
    c.created_by,
    r.service_description_short, 
    r.version, 
    r.outsourcer AS outsourcer_id, 
    o.name AS outsourcer_name, 
    r.insourcer AS insourcer_id,
    i.name AS 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 risk_ranked r 
    ON r.outsourcing_id = c.id 
    AND r.rn = 1
LEFT JOIN OS_COMPANY_INSOURCER i 
    ON i.id = r.insourcer
LEFT JOIN OS_COMPANY_OUTSOURCER o 
    ON o.id = r.outsourcer

内容的提问来源于stack exchange，提问作者Jesus Paradinas