Hola equipo, esta era mi consulta de Oracle existente en sintaxis antigua

select * from
USER us,
USER_EXTGBL ue,
STTMS_BRANCH_EXTGBL be,
ROLE_MASTER rm,
USER_ROLE ur
where us.user_id = ue.USER_ID
and us.USER_ID = ur.user_id(+)
and ur.ROLE_ID = rm.ROLE_ID
and us.HOME_BRANCH = be.BRANCH_CODE 
and us.AUTH_STAT='A' and us.RECORD_STAT='O'
and rm.AUTH_STAT='A' and rm.RECORD_STAT='O' 
and us.HOME_LEGAL_VEHICLE = ur.LEGAL_VEHICLE_CODE(+)
and us.HOME_BRANCH = ur.branch_code(+) 
and us.home_branch in (select branch_code from sttms_branch where country_code= $Country_Code)

Me enfrento a desafíos para reescribir en una nueva sintaxis de Oracle, ya que cuando uso la combinación izquierda / derecha en el lugar en el que no puedo usar la condición where. ¿Puede alguien ayudarme a reescribir la consulta?

-2
NMB 26 abr. 2017 a las 23:18

3 respuestas

La mejor respuesta

USER_ROLE solo está aparentemente unido externamente. En realidad, ur.ROLE_ID = rm.ROLE_ID se asegura de que todos los registros unidos externamente se descarten y que la unión sea finalmente una unión interna. Entonces, todo es uniones internas:

select * 
from user us
join user_extgbl ue on us.user_id = ue.user_id
join sttms_branch_extgbl be on us.home_branch = be.branch_code 
join user_role ur on  us.user_id            = ur.user_id
                  and us.home_legal_vehicle = ur.legal_vehicle_code
                  and us.home_branch        = ur.branch_code
join role_master rm on  ur.role_id     = rm.role_id
                    and us.auth_stat   = rm.auth_stat
                    and us.record_stat = rm.record_stat
where us.auth_stat = 'A' 
  and us.record_stat = 'O'
  and us.home_branch in 
        (select branch_code from sttms_branch where country_code= $country_code);

Si desea unir externamente USER_ROLE, también debe unir externamente ROLE_MASTER.

2
Thorsten Kettner 26 abr. 2017 a las 20:39
    SELECT *
  FROM USER us
       INNER JOIN user_extgbl ue ON us.user_id = ue.user_id
       INNER JOIN user_role ur
           ON us.user_id = ur.user_id
          AND us.home_legal_vehicle = ur.legal_vehicle_code
          AND us.home_branch = ur.branch_code
       INNER JOIN sttms_branch_extgbl be ON us.home_branch = be.branch_code
       INNER JOIN role_master rm ON ur.role_id = rm.role_id
 WHERE us.auth_stat = 'A'
   AND us.record_stat = 'O'
   AND rm.auth_stat = 'A'
   AND rm.record_stat = 'O'
   AND us.home_branch IN (SELECT branch_code
                            FROM sttms_branch
                           WHERE country_code = country_code)
0
Brian Leach 27 abr. 2017 a las 18:10
select *
from   USER us
       INNER JOIN USER_EXTGBL ue
         ON ( us.user_id = ue.USER_ID )
       LEFT OUTER JOIN USER_ROLE ur
         ON (    us.USER_ID = ur.user_id
             AND us.HOME_LEGAL_VEHICLE = ur.LEGAL_VEHICLE_CODE
             AND us.HOME_BRANCH = ur.branch_code )
       INNER JOIN ROLE_MASTER rm
         ON ( ur.ROLE_ID = rm.ROLE_ID )
       INNER JOIN STTMS_BRANCH_EXTGBL be
         ON ( us.HOME_BRANCH = be.BRANCH_CODE )
where  us.AUTH_STAT='A' and us.RECORD_STAT='O'
and    rm.AUTH_STAT='A' and rm.RECORD_STAT='O' 
and    us.home_branch in ( select branch_code
                           from   sttms_branch
                           where  country_code = $Country_Code )
0
MT0 26 abr. 2017 a las 20:28