migrate query sybase to oracle with sign "*="-CodePudding

Hello guys i have this query in sybase with this sign *=, I use in oracle LEFT OUTER JOIN but i dont if right.

Query in sybase:

select
  right(( "00"  convert(varchar(2), cta_consol.cod_correo)) , 2)  
  right(("000000000"   convert(varchar(11), cta_consol.num_cta_cte)), 9) 
from
  t_cuenta_consolidada cta_consol,
  t_cuenta_corriente   ctacte ,
  t_empresa            empresa,
  t_comuna             comuna,
  t_comuna             comuna_CtaCte,
  t_ciudad             ciudad,
  t_ciudad             ciudad_CtaCte ,
  t_cta_cte_param         param
where
  empresa.ide              = cta_consol.ide_cliente  and
  empresa.cod_comuna       = comuna.codigo           and
  comuna.cod_ciudad        = ciudad.codigo           and
  ctacte.cod_comuna        = comuna_CtaCte.codigo    and
  comuna_CtaCte.cod_ciudad = ciudad_CtaCte.codigo    and
  cta_consol.num_cta_cte   = ctacte.num_cta_cte      and
  cta_consol.num_cta_cte   *= param.num_cta_cte

cta_consol.num_cta_cte *= param.num_cta_cte

query migrate to oracle:

 select 
  SUBSTR(('00' || CAST(cta_consol.cod_correo AS VARCHAR2(2))),-2) ||
  SUBSTR(('000000000' || CAST(cta_consol.num_cta_cte AS VARCHAR2(11))),-9) 
from
  t_cuenta_consolidada cta_consol 
LEFT OUTER JOIN t_cta_cte_param param ON cta_consol.num_cta_cte = param.num_cta_cte, 
  t_cuenta_corriente   ctacte, 
  t_empresa empresa, 
  t_comuna comuna, 
  t_ciudad  ciudad
where
  empresa.ide = cta_consol.ide_cliente AND 
  empresa.cod_comuna  = comuna.codigo AND 
  comuna.cod_ciudad  = ciudad.codigo AND 
  ctacte.cod_comuna  = comuna.codigo AND 
  comuna.cod_ciudad = ciudad.codigo AND 
  cta_consol.num_cta_cte  = ctacte.num_cta_cte

LEFT OUTER JOIN t_cta_cte_param param ON cta_consol.num_cta_cte = param.num_cta_cte,

I have compared the amount of registration of each query, and it is not the same

CodePudding user response：

You can mix modern ANSI join syntax with the old proprietary Oracle join syntax, but the order is important.

It's usually easier and less confusing to either do ANSI:

select 
    SUBSTR(('00' || CAST(cta_consol.cod_correo AS VARCHAR2(2))),-2) ||
    SUBSTR(('000000000' || CAST(cta_consol.num_cta_cte AS VARCHAR2(11))),-9) 
from
    t_cuenta_consolidada cta_consol 
LEFT JOIN t_cta_cte_param param ON cta_consol.num_cta_cte = param.num_cta_cte
JOIN t_cuenta_corriente ctacte ON cta_consol.num_cta_cte  = ctacte.num_cta_cte
JOIN t_comuna comuna ON ctacte.cod_comuna  = comuna.codigo
JOIN t_empresa empresa ON empresa.ide = cta_consol.ide_cliente AND empresa.cod_comuna  = comuna.codigo 
JOIN t_ciudad  ciudad ON comuna.cod_ciudad  = ciudad.codigo
WHERE ...

Or old Oracle style:

select 
    SUBSTR(('00' || CAST(cta_consol.cod_correo AS VARCHAR2(2))),-2) ||
    SUBSTR(('000000000' || CAST(cta_consol.num_cta_cte AS VARCHAR2(11))),-9) 
from
t_cuenta_consolidada cta_consol,
t_cta_cte_param param, 
t_cuenta_corriente   ctacte, 
t_empresa empresa, 
t_comuna comuna, 
t_ciudad  ciudad
where
empresa.ide = cta_consol.ide_cliente AND 
empresa.cod_comuna  = comuna.codigo AND 
comuna.cod_ciudad  = ciudad.codigo AND 
ctacte.cod_comuna  = comuna.codigo AND 
comuna.cod_ciudad = ciudad.codigo AND 
cta_consol.num_cta_cte  = ctacte.num_cta_cte AND
cta_consol.num_cta_cte = param.num_cta_cte ( )