Postgres OUTER JOINS TABLE ALREADY JOINED-CodePudding

i'm facing issues migrating queries from Oracle to Postgres 15. How can i join a table that is already joined? The Oracle looks like this:

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

When i try to rewrite it on postgres, the table gdf_suez.ld_documenti ld needs to be joined to 3 tables, and one of those 3 needs to be joined to another table. How do i fix this?

Tried to rewrite the query but the error "table name "ld" specified more than once" is thrown

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

CodePudding user response：

It is always easy to translate from Oracle's outdated outer join syntax to the more powerful syntax of the SQL standard: put everything that has a ( ) on the right side of a LEFT JOIN. So your FROM clause would become

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