For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!
Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.
Hi,
I am totally new to the CDB Architecture.
I want to create for the same time a DB Instance (Single Tenant) with the ENABLE PLUGGABLE DATABASE clause.
Can I use the same ORACLE HOME that I used so far for the non CDB Instance?
Thanks!
Hi, User_TDDEP In the query you posted, there is no relationship between departments and jobs, that is, there is a cross-join; then employees is outer-joined to that result set. Here's one way to do it in ANSI notation:
SELECT e.*, d.*, j.* FROM departments d CROSS JOIN jobs j LEFT JOIN employees e ON e.department_id = d.department_id AND e.job_id = 'HR REP' AND j.job_id = e.job_id ORDER BY e.department_id ;
As you wrote - it is a cartesian product. ok - now it is clear for me. and in case of simple condition SELECT E.*, D.*, J.* FROM EMPLOYEES E, DEPARTMENTS D, JOBS J WHERE E.DEPARTMENT_ID(+)=D.DEPARTMENT_ID AND E.JOB_ID(+)=J.JOB_ID AND E.JOB_ID(+)='FI_ACCOUNT' AND E.LAST_NAME='Lorentz' --added cond. ORDER BY E.DEPARTMENT_ID; ; the AND E.LAST_NAME='Lorentz' --added cond. will be rewrite to the separeted WHERE clause like this one true?
SELECT e.*, d.*, j.* FROM departments d CROSS JOIN jobs j LEFT JOIN employees e ON (e.department_id = d.department_id AND e.job_id = 'FI_ACCOUNT' AND j.job_id = e.job_id) WHERE E.LAST_NAME='Lorentz' ORDER BY e.department_id ;
Hi, User_TDDEP The two queries you posted half an hour ago are equivalent. The conditions AND E.JOB_ID(+)='FI_ACCOUNT' AND E.LAST_NAME='Lorentz' are fundamentally different. The former, using (+), means "don't exclude rows just because of the job_id", but the latter means "do exclude rows if they don't have the right last_name". Another way to get the same results is
SELECT e.*, d.*, j.* FROM departments d JOIN employees e ON e.department_id = d.department_id JOIN jobs j ON j.job_id = e.job_id WHERE e.job_id = 'FI_ACCOUNT' AND e.last_name = 'Lorentz' ORDER BY e.department_id ;
using inner joins
Hi @User_TDDEP As you wrote - it is a cartesian product. so it should be (the script needs to be changed/improved if necessary) DEPARTMENTS and JOBS are not related, so Cartesian magic data E.JOB_ID='HR REP' one row in the older oracle version we will get an error
SQL> / WHERE E.DEPARTMENT_ID(+)=D.DEPARTMENT_ID * ERROR at line 12: ORA-01417: a table may be outer joined to at most one other table
see example (DEPARTMENTS and JOBS have only 2 rows)
with EMPLOYEES (DEPARTMENT_ID,JOB_ID,ENAME) as (select 10,'HR REP','JONES' from dual ) ,DEPARTMENTS (DEPARTMENT_ID,DNAME) as (select 10,'Administration' from dual union all select 210,'IT Support' from dual ) ,JOBS (JOB_ID,JOB_TITLE) as ( select 'HR REP','HRR' from dual union all select 'IT_PROG','Programmer' from dual ) SELECT J.*,E.*, D.* FROM EMPLOYEES E, DEPARTMENTS D, JOBS J WHERE E.DEPARTMENT_ID(+)=D.DEPARTMENT_ID AND E.JOB_ID(+)=J.JOB_ID AND E.JOB_ID(+)='HR REP' ORDER BY E.DEPARTMENT_ID / JOB_ID JOB_TITLE DEPARTMENT_ID JOB_ID ENAME DEPARTMENT_ID DNAME HR REP HRR 10 HR REP JONES 10 Administration IT_PROG Programmer - - - 210 IT Support HR REP HRR - - - 210 IT Support IT_PROG Programmer - - - 10 Administration 4 rows returned in 0.00 seconds Download
What about rewriting this one to ANSI JOIN e.g. select ts.employee_id from JOB_HISTORY jh, (SELECT E.*, D.*, J.* FROM EMPLOYEES E, DEPARTMENTS D, JOBS J WHERE E.DEPARTMENT_ID(+)=D.DEPARTMENT_ID AND E.JOB_ID(+)=J.JOB_ID AND E.JOB_ID(+)='FI_ACCOUNT' AND E.LAST_NAME='Lorentz' --added cond. ) ts where jh.employee_id(+)=ts.employee_id;
Hi, User_TDDEP What about rewriting this one to ANSI JOIN e.g. Here's one way, that matches the query you posted very closely:
SELECT ts.employee_id FROM ( SELECT e.* -- , ... add columns from tables d and j if wanted FROM departments d CROSS JOIN jobs j LEFT JOIN employees e ON e.department_id = d.department_id AND e.job_id = 'FI_ACCOUNT' AND e.job_id = j.job_id WHERE e.last_name = 'Lorentz' --added cond. ) ts LEFT JOIN job_history jh ON jh.employee_id = ts.employee_id ;
It's interesting that the query you posted runs at all, since ts duplicate column names. For some reason, the query I posted raises a "ORA-00918: column ambiguously defined" error if ts has duplicate column names, even though those columns are not used anywhere in the query. If you really want to, you can add all the columns from d and j to the result set of ts, just make sure they have distinct aliases. Again, since you're essentially doing an inner join with the employees table, the query above is just a convoluted way of saying:
SELECT e.employee_id FROM departments d JOIN employees e ON e.department_id = d.department_id AND e.job_id = 'FI_ACCOUNT' JOIN jobs j ON j.job_id = e.job_id LEFT JOIN job_history jh ON jh.employee_id = e.employee_id WHERE e.last_name = 'Lorentz' --added cond. ;