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.

Improve this question
2
  • what's the difference in results? (don't force us to guess, please) Commented Apr 16, 2020 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. Commented Apr 16, 2020 at 19:34

1 Answer 1

Reset to default
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.

Improve this answer
5
  • When adding ch.cod_ent in (...) the query does not work. That's why i used the JOIN Commented Apr 16, 2020 at 19:59
  • @bough: well that changes the meaning of the query. What is the error you get? Commented Apr 16, 2020 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 Commented Apr 16, 2020 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) ? Commented Apr 17, 2020 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 Commented Apr 17, 2020 at 9:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.