Oracle转PostgreSQL 15查询迁移：重复连接表报错解决

你遇到的错误核心是重复引用了ld_documenti表并使用同一个别名ld，同时混合旧式逗号连接和显式JOIN导致逻辑混乱。以下是正确的改写方案，完全贴合原Oracle查询的逻辑：

正确的PostgreSQL查询代码 #

SELECT 
    ltd1.short_description || '_' || TO_CHAR(
        CASE WHEN lsd.origine_type_id = 2 THEN lsd.data_normalizzazione_mail 
             ELSE lsd.data_normalizzazione_web 
        END, 'YYYYMMDD_HH24MISS'
    ) AS Transazione,
    lkc.kit_name || '_' || lkc.doc_name AS Documento_Idkit_iddoc,
    lp.codice_pratica AS Pratica,
    TO_CHAR(
        CASE WHEN lsd.origine_type_id = 2 THEN lsd.data_normalizzazione_mail 
             ELSE lsd.data_normalizzazione_web 
        END, 'DD/MM/YYYY HH24:MI:SS'
    ) AS Data_Inizio,
    ltd1.long_description AS Tipologia_Documento,
    CASE WHEN lsd.origine_type_id = 2 THEN 'Digitale' ELSE 'Cartaceo' END AS Tipologia_Arrivo,
    lsc.sla_period || ' ore' AS Sla,
    TO_CHAR(lsd.data_fine_trans, 'DD/MM/YYYY HH24:MI:SS') AS Data_Fine_Lavorazione,
    ltd2.short_description AS Fase_Lavorazione,
    CASE WHEN ((lsd.sla_period * 60) - lsd.min_elapsed) <= 0 THEN 999999999 
         ELSE ((lsd.sla_period * 60) - lsd.min_elapsed) 
    END AS Tempo_Residuo,
    TO_CHAR(lsd.data_prevista_fine_sla, 'DD/MM/YYYY HH24:MI:SS') AS Data_Previsione_Chiusura_SLA,
    lsd.alert_sla AS Alert,
    lsd.stato_sla_type_id AS Stato_Lav,
    lsd.in_sla,
    lsd.out_of_sla AS OutSla
FROM gdf_suez.ld_sla_detail lsd
-- 内连接类型描述表（DOC_TYPE_ID）
INNER JOIN gdf_suez.ls_type_description ltd1 
    ON ltd1.context = 'DOC_TYPE_ID' 
    AND ltd1.type_id = lsd.doc_type_id
-- 内连接SLA配置表
INNER JOIN gdf_suez.ls_sla_config lsc 
    ON lsc.doc_type_id = lsd.doc_type_id 
    AND lsc.doc_subtype_id = lsd.doc_subtype_id 
    AND lsc.giorno_id = 1
-- 内连接状态类型描述表
INNER JOIN gdf_suez.ls_type_description ltd2 
    ON ltd2.context = 'DOC_STATUS_TYPE_ID' 
    AND ltd2.type_id = lsd.stato_sla_type_id
-- 左外连接文档表
LEFT JOIN gdf_suez.ld_documenti ld 
    ON ld.trans_id = lsd.trans_id
-- 通过文档表左外连接套件配置表
LEFT JOIN gdf_suez.ls_kit_config lkc 
    ON lkc.kit_id = ld.kit_id
-- 通过文档表左外连接业务表
LEFT JOIN gdf_suez.ld_pratiche lp 
    ON lp.pratica_id = ld.pratica_id
-- 左外连接邮件表，注意将过滤条件放到ON子句避免转为内连接
LEFT JOIN gdf_suez.ld_mail lm 
    ON lm.trans_id = lsd.trans_id 
    AND lm.email_stato_type_id NOT IN (6,16)
WHERE 
    -- 替换Oracle的decode为CASE
    CASE WHEN lsd.origine_type_id = 2 THEN lsd.data_normalizzazione_mail 
         ELSE lsd.data_normalizzazione_web 
    END BETWEEN '2022-02-01'::DATE AND '2022-02-28 23:59:59'::TIMESTAMP
    AND lsd.stato_trans_type_id >= 0
ORDER BY Alert DESC, Stato_Lav, Tempo_Residuo;

关键改写说明 #

1. 消除重复表引用：只引用一次ld_documenti表，后续通过它关联ls_kit_config和ld_pratiche，避免重复别名冲突。
2. 统一显式JOIN语法：完全抛弃旧式逗号连接，改用显式INNER JOIN/LEFT JOIN，逻辑更清晰，避免歧义。
3. 外连接条件处理：将lm.email_stato_type_id NOT IN (6,16)放到LEFT JOIN的ON子句中，否则PostgreSQL会将左外连接自动转为内连接（和Oracle中(+)加where条件的行为一致）。
4. 函数替换：用PostgreSQL支持的CASE语句替代Oracle的decode函数，保持逻辑一致。
5. 日期简化：直接用::DATE/::TIMESTAMP类型转换替代Oracle的to_date/to_timestamp，PostgreSQL支持直接解析标准格式字符串。

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