Postgresql – Migrate SELECT query from oracle to postgresql

migrationoraclepostgresqlselect

I want to migrate an oracle query to postgresql :

here is the oracle version : 

SELECT
replace(replace(replace(
cf.NO_FACTURE||';'||
cf.MODULE||';'||
replace(replace(replace(cf.REF_FACTURE_SYBEL,'&',' '),'''',''''''),';','')||';'||
nvl(replace(to_char(cf.SOLDE_VERSE),',','.'),0)||';'||
cf.DATE_PAIEMENT_FACTURE||';'||
nvl(replace(to_char(cf.MONTANT_ACOMPTE_VERSE),',','.'),0)
, CHR(10),''),CHR(13),''),'*',' ')
        FROM
            (SELECT distinct td_inf.NO_FACTURE
             FROM tac_dossier td_inf,
                  com_agent ca,
                  com_habilitation ch
             WHERE td_inf.etat_dossier = 'id.15.ClosFacturation'
             AND   MONTHS_BETWEEN(sysdate,td_inf.date_chgt_etat) > $nb_mois_clos
             AND   td_inf.cod_ag_resp = ca.cod_ag(+)
             AND   ch.cod_ag = ca.cod_ag
             AND   ch.cod_ent in (select cod_ent from com_ent_ft where pole = td_inf.cod_ent or cod_ent = td_inf.cod_ent)) td,
             COM_FACTURE cf
        WHERE cf.NO_FACTURE = td.NO_FACTURE;

and here is my postgresql version of the query : 

SELECT
replace(replace(replace(
cf.NO_FACTURE||';'||
cf.MODULE||';'||
replace(replace(replace(cf.REF_FACTURE_SYBEL,'&',' '),'''',''''''),';','')||';'||
COALESCE(replace(to_char(cf.SOLDE_VERSE),',','.'),'0')||';'||
cf.DATE_PAIEMENT_FACTURE||';'||
COALESCE(replace(to_char(cf.MONTANT_ACOMPTE_VERSE),',','.'),'0')
, CHR(10),''),CHR(13),''),'*',' ')
        FROM
            (SELECT distinct td_inf.NO_FACTURE 
             FROM 
                  tac_dossier td_inf
                  LEFT OUTER JOIN com_agent ca ON td_inf.cod_ag_resp = ca.cod_ag
                  INNER JOIN com_ent_ft cef ON (cef.pole = td_inf.cod_ent or cef.cod_ent = td_inf.cod_ent)
                  INNER JOIN com_habilitation ch ON (ch.cod_ag = ca.cod_ag and ch.cod_ent = cef.cod_ent)
             WHERE td_inf.etat_dossier = 'id.15.ClosFacturation'
             AND   EXTRACT(month from age(NOW(),td_inf.date_chgt_etat)) > 2 
             ) td,
             COM_FACTURE cf
        WHERE cf.NO_FACTURE = td.NO_FACTURE;

The query is working there is no problem in the syntax But the results of the two queries are different.

Any note on this ?

Thank you.

Best Answer

This: 

AND   EXTRACT(month from age(NOW(),td_inf.date_chgt_etat)) > 2

is something different then the original Oracle condition.

The extract functions does not get the equivalent of the interval in months, but simply the "month" field. So if the interval is "1 year 0 months 2 days 16 hours", extract(month from ...) will return 0.

What you want is: 

and age(now(), td_inf.date_chgt_etat) > interval '2 months'

or if you want to make that use an index on the column: 

and td_inf.date_chgt_etat < now() - interval '2 months'

Your query is also missing the

AND   ch.cod_ent in (...)

You have translated that to a JOIN which does something different. Whether or not that changes the result depends on how the two tables com_ent_ft and com_habilitation are related.

Related Question