From Oracle to Postgres

Question

We tried to migrate from Oracle to Postgres. We use ora2pg , but we have an error with this code:

SELECT DISTINCT UPU.USUA_C_USUARIO
  FROM GN_USUARIOS U,TR_USUARIOS_X_PERFILES_USUARIO UPU,TR_V_PERFILES_USUARIOS PU
  WHERE (U.C_USUARIO = UPU.USUA_C_USUARIO(+)) 
    AND (UPU.PEUS_X_PEUS = PU.X_PEUS) 
    AND U.C_USUARIO = USU.C_USUARIO)) 
   OR NOT EXISTS (
   SELECT UPU2.USUA_C_USUARIO 
     FROM TR_USUARIOS_X_PERFILES_USUARIO UPU2 
     WHERE UPU2.USUA_C_USUARIO = USU.C_USUARIO) 
     OR USER = (
        SELECT V_CONSTANTE 
         FROM GN_CONSTANTES 
         WHERE C_CONSTANTE = 'TRUSUPROP')

We have an error with PU.USUA_C_USUARIO(+). We dont have enough experience in this kind of conversions. How can we transform the code with LEFT OUTER JOIN ?

Thanks!

score 0 · Answer 1 · edited Feb 01 '17 at 19:49

0

Probably like so:

FROM GN_USUARIOS U
LEFT JOIN TR_USUARIOS_X_PERFILES_USUARIO UPU ON U.C_USUARIO = UPU.USUA_C_USUARIO
LEFT JOIN TR_V_PERFILES_USUARIOS PU ON UPU.PEUS_X_PEUS = PU.X_PEUS
WHERE U.C_USUARIO = USU.C_USUARIO)

Or possibly:

FROM GN_USUARIOS U
LEFT JOIN ( SELECT UPU.USUA_C_USUARIO, ...
            FROM TTR_USUARIOS_X_PERFILES_USUARIO UPU
            JOIN TR_V_PERFILES_USUARIOS PU ON UPU.PEUS_X_PEUS = PU.X_PEUS
          ) UPU_PU ON U.C_USUARIO = UPU_PU.USUA_C_USUARIO
WHERE U.C_USUARIO = USU.C_USUARIO)

In either case, be wary that there's a USU table that is defined nowhere and mismatched parenthesis in your query.

edited Feb 01 '17 at 19:49

Vao Tsun

47,234
13
100
132

answered Nov 21 '13 at 11:00

Denis de Bernardy

75,850
13
131
154

My query is the part with the problem, USU is defined in the entire query. And the lost parenthesis is an editing problem. :) The two solutions work perfectly. Thanks. – Luallo Nov 21 '13 at 16:12

From Oracle to Postgres

1 Answers1