CODEWITHSUNDEEP
sqloracle-databaseoracle-sqldeveloper
AMB
AMB

Reputation: 1035

IMPLICIT and EXPLICIT join

im trying to rewrite the following code:

W_WHERE := ' PD.NIF(+) = p.NIF and pd.num_colegiado(+) = p.num_colegiado AND PD.FECHA_INICIO(+) <= SYSDATE 
       AND NVL(PD.FECHA_FIN(+), SYSDATE) >= SYSDATE AND D.ID_DIRECCION(+) = PD.ID_DIRECCION AND p.num_colegiado  = coleg.num_colegiado';

into normal JOIN notation, could anybody help me ?

PS. PD is for PERSONA_DIRECCION table and P is for PERSONA table

Upvotes: 1

Views: 1237

Answers (2)

Thorsten Kettner
Thorsten Kettner

Reputation: 94894

Explicit joins get their name from specifying explicitly what kind of join you use on the table (CROSS JOIN, INNER JOIN, LEFT OUTER JOIN etc.)

So you will have to re-write the query such that you replace the comma-separated tables in your FROM clause with explicit joins (INNER JOIN and LEFT JOIN here). Then move your join criteria to the ON clause in question:

select ...
from colleg
inner join p on p.num_colegiado  = coleg.num_colegiado
left join pd on pd.nif = p.nif and 
                pd.num_colegiado = p.num_colegiado and 
                pd.fecha_inicio <= sysdate and
                nvl(pd.fecha_fin, sysdate) >= sysdate
left join d on d.id_direccion = pd.id_direccion;

Upvotes: 2

J&#233;r&#244;me Radix
J&#233;r&#244;me Radix

Reputation: 10533

There is nothing implicit here. In Oracle, "(+) =" is a "normal JOIN notation" (as you said) for outer join. If you don't want outer join, just remove the (+).

See this SO answer for the explanation.

Upvotes: 2

Related Questions