Error when running a sub query in Oracle SQL

Question

I am trying to join three tables using a sub query.

The result of the first left outer join is to be used with another table to get a composite view with all attributes.

I am getting an error where the compile says, Unknown Command for the table in the second join clause.

When I create two independent views and then join then it works fine.

(select
l.ENROLLED_CONTENT,
l.LEARNING_ENROLLMENT_LEARNER,
l.EMPLOYEE_ID,
l.JOB_FAMILY_GROUP,
l.EMPLOYEE_TYPE,
l.JOB_FAMILY,
l.LEARNING_ENROLLMENT,
l.COMPLETION_STATUS,
l.COMPLETION_DATE,
l.EXPIRATION_DATE,
l.CF_LRV_LEARNING_CONTENT_NUMBER,
l.LEARNING_CONTENT_DETAIL,
l.LEARNING_CONTENT_TYPE,
l.LESSON_TYPE,
e.id# "WK_WORKER_ID"
from tgt_workday.learning l
left outer join ods_hrmaster.employee e
on l.EMPLOYEE_ID = e.employee#) t1
left outer join ( select
    per_ids_id,
    per_id,
    id_pureid from
    ods_pure.person_ids
) t2 on t1.wk_worker_id = t2.value where t2.type = 'Employee ID';

Popeye · Accepted Answer · 2020-12-03 05:39:36Z

You can write it in a simple way. There is no need to make sub-queries as:

SELECT L.ENROLLED_CONTENT,
       L.LEARNING_ENROLLMENT_LEARNER,
       L.EMPLOYEE_ID,
       L.JOB_FAMILY_GROUP,
       L.EMPLOYEE_TYPE,
       L.JOB_FAMILY,
       L.LEARNING_ENROLLMENT,
       L.COMPLETION_STATUS,
       L.COMPLETION_DATE,
       L.EXPIRATION_DATE,
       L.CF_LRV_LEARNING_CONTENT_NUMBER,
       L.LEARNING_CONTENT_DETAIL,
       L.LEARNING_CONTENT_TYPE,
       L.LESSON_TYPE,
       E.ID# "WK_WORKER_ID"
  FROM TGT_WORKDAY.LEARNING    L
  LEFT OUTER JOIN ODS_HRMASTER.EMPLOYEE   E
ON L.EMPLOYEE_ID = E.EMPLOYEE#
  LEFT OUTER JOIN ODS_PURE.PERSON_IDS     T2
ON E.ID# = T2.VALUE
   AND T2.TYPE = 'Employee ID';

Once you use the outer joined table's column in WHERE clause, It will result in the same result as inner join(there is another ways to use it in WHERE clause though). So it is better to avoid using outer joined table's column in the WHERE clause.

Jacob · Accepted Answer · 2020-12-03 04:26:28Z

1

Try as

SELECT *
  FROM ( (SELECT l.ENROLLED_CONTENT,
                 l.LEARNING_ENROLLMENT_LEARNER,
                 l.EMPLOYEE_ID,
                 l.JOB_FAMILY_GROUP,
                 l.EMPLOYEE_TYPE,
                 l.JOB_FAMILY,
                 l.LEARNING_ENROLLMENT,
                 l.COMPLETION_STATUS,
                 l.COMPLETION_DATE,
                 l.EXPIRATION_DATE,
                 l.CF_LRV_LEARNING_CONTENT_NUMBER,
                 l.LEARNING_CONTENT_DETAIL,
                 l.LEARNING_CONTENT_TYPE,
                 l.LESSON_TYPE,
                 e.id# "WK_WORKER_ID"
            FROM tgt_workday.learning l
                 LEFT OUTER JOIN ods_hrmaster.employee e
                    ON l.EMPLOYEE_ID = e.employee) t1
        LEFT OUTER JOIN
        (SELECT per_ids_id, per_id, id_pureid FROM ods_pure.person_ids) t2
           ON t1.wk_worker_id = t2.VAL AND t2.TYPE = 'Employee ID')

answered Dec 3, 2020 at 4:26

Jacob

14.8k69 gold badges215 silver badges323 bronze badges

1 Comment

Vijay Over a year ago

Thanks Jacob, added value and type in the inner select clause and the result came as expected :).

Collectives™ on Stack Overflow

Error when running a sub query in Oracle SQL

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related