Postgres OUTER JOINS TABLE ALREADY JOINED

Question

我正面临从Oracle迁移查询到Postgres 15的问题。如何加入已加入的表?Oracle是这样的:

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' ) Transazione, 
lkc.kit_name || '_' || lkc.doc_name Documento_Idkit_iddoc, 
lp.codice_pratica 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' ) Data_Inizio, 
ltd1.long_description Tipologia_Documento, 
CASE WHEN lsd.origine_type_id = 2 THEN 'Digitale' ELSE 'Cartaceo' END Tipologia_Arrivo, 
lsc.sla_period || ' ore' Sla, 
to_char( lsd.data_fine_trans, 'DD/MM/YYYY HH24:MI:SS' ) Data_Fine_Lavorazione, 
ltd2.short_description Fase_Lavorazione, 
case when ((lsd.sla_period * 60) - lsd.min_elapsed) <= 0 then 999999999 else ((lsd.sla_period * 60) - lsd.min_elapsed) end Tempo_Residuo, 
to_char( lsd.data_prevista_fine_sla, 'DD/MM/YYYY HH24:MI:SS' ) Data_Previsione_Chiusura_SLA, 
lsd.alert_sla Alert,  
lsd.stato_sla_type_id Stato_Lav, 
lsd.in_sla, 
lsd.out_of_sla OutSla 
from gdf_suez.ld_sla_detail lsd, 
gdf_suez.ls_type_description ltd1, 
gdf_suez.ld_documenti ld, 
gdf_suez.ls_kit_config lkc, 
gdf_suez.ld_pratiche lp, 
gdf_suez.ls_sla_config lsc, 
gdf_suez.ls_type_description ltd2, 
gdf_suez.ld_mail lm  
where ltd1.context = 'DOC_TYPE_ID' 
and ltd1.type_id = lsd.doc_type_id 
and ld.trans_id(+) = lsd.trans_id 
and lm.trans_id(+) = lsd.trans_id 
and lkc.kit_id(+) = ld.kit_id 
and lp.pratica_id(+) = ld.pratica_id 
and lsc.doc_type_id = lsd.doc_type_id 
and lsc.doc_subtype_id = lsd.doc_subtype_id 
and lsc.giorno_id = 1 
and ltd2.context = 'DOC_STATUS_TYPE_ID' 
and ltd2.type_id = lsd.stato_sla_type_id 
and decode( lsd.origine_type_id, 2, lsd.data_normalizzazione_mail, lsd.data_normalizzazione_web) between to_date( '2022-02-01', 'YYYY-MM-DD' ) and to_timestamp( '2022-02-28 23:59:59', 'YYYY-MM-DD HH24:MI:SS' ) 
and lsd.stato_trans_type_id >= 0 
and lm.email_stato_type_id not in (6,16) 
order by Alert desc, Stato_Lav, Tempo_Residuo

当我尝试在postgres上重写它时，表gdf_suez。Ld_documenti ld需要连接到3个表，其中一个需要连接到另一个表。我怎么解决这个问题?

试图重写查询，但错误"表名"&;ld"指定了不止一次">

抛出

from gdf_suez.ld_sla_detail lsd 
LEFT OUTER JOIN  gdf_suez.ld_documenti ld ON lsd.trans_id = ld.trans_id
LEFT OUTER JOIN  gdf_suez.ld_mail lm ON lsd.trans_id = lm.trans_id,
gdf_suez.ls_type_description ltd1, 
gdf_suez.ld_documenti ld 
LEFT OUTER JOIN gdf_suez.ls_kit_config lkc ON ld.kit_id = lkc.kit_id
LEFT OUTER JOIN gdf_suez.ld_pratiche lp ON ld.pratica_id = lp.pratica_id,
gdf_suez.ls_sla_config lsc, 
gdf_suez.ls_type_description ltd2

Answer 1

将Oracle过时的外连接语法转换为SQL标准中更强大的语法总是很容易的:将所有具有(+)的内容放在LEFT JOIN的右侧。所以你的FROM子句会变成

FROM gdf_suez.ls_type_description AS ltd1
JOIN gdf_suez.ld_sla_detail AS lsd
ON ltd1.type_id = lsd.doc_type_id
JOIN gdf_suez.ls_sla_config AS lsc
ON lsc.doc_type_id = lsd.doc_type_id
AND lsc.doc_subtype_id = lsd.doc_subtype_id 
JOIN gdf_suez.ls_type_description AS ltd2
ON ltd2.type_id = lsd.stato_sla_type_id
LEFT JOIN gdf_suez.ld_mail AS lm
ON lm.trans_id = lsd.trans_id
LEFT JOIN gdf_suez.ld_documenti AS ld
ON ld.trans_id = lsd.trans_id
LEFT JOIN gdf_suez.ls_kit_config AS lkc
ON lkc.kit_id = ld.kit_id
LEFT JOIN gdf_suez.ld_pratiche AS lp
ON lp.pratica_id = ld.pratica_id