CODEWITHSUNDEEP
oracle-database
Vijay
Vijay

Reputation: 49

Error when running a sub query in Oracle SQL

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';

Upvotes: 1

Views: 55

Answers (2)

Popeye
Popeye

Reputation: 35900

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.

Upvotes: 1

Jacob
Jacob

Reputation: 14731

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')

Upvotes: 1

Related Questions