0

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.

2
  • what's the difference in results? (don't force us to guess, please) – filiprem Apr 16 '20 at 19:14
  • What does it do? What is the purpose of all the nested replace() calls? I wouldn't be surprised if there is a much better alternative. – a_horse_with_no_name Apr 16 '20 at 19:34
1

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.

5
  • When adding ch.cod_ent in (...) the query does not work. That's why i used the JOIN – unknownUser Apr 16 '20 at 19:59
  • @bough: well that changes the meaning of the query. What is the error you get? – a_horse_with_no_name Apr 16 '20 at 20:04
  • actually i don't get an explicit error , when executing the query , it's like if it is blocked , no results are being displayed , i think that it is doing too much stuff in the background – unknownUser Apr 16 '20 at 20:09
  • Is there any way to make the query work with : sql ch.cod_ent in (select cod_ent from com_ent_ft where pole = td_inf.cod_ent or cod_ent = td_inf.cod_ent) ? – unknownUser Apr 17 '20 at 9:27
  • Well, it's syntactically correct. Without more details it's impossible to know why it doesn't run. You could try to rewrite it to an EXISTS condition rather than an IN – a_horse_with_no_name Apr 17 '20 at 9:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.