时间戳

首页 > 解决方案 > 在 Oracle SQL 中运行子查询时出错

oracle - 在 Oracle SQL 中运行子查询时出错

问题描述

我正在尝试使用子查询连接三个表。

第一个左外连接的结果将与另一个表一起使用以获得具有所有属性的复合视图。

我在编译时遇到错误，第二个连接子句中的表的未知命令。

当我创建两个独立的视图然后加入时，它工作正常。

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

标签： oracle

解决方案

试试看

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

推荐阅读