ORA-00918:SELECT*中定义不明确的列

Question

Getting ORA-00918：列定义不明确：运行此SQL:

SELECT *
FROM
  (SELECT DISTINCT(coaches.id),
    people.*,
    users.*,
    coaches.*
  FROM "COACHES"
  INNER JOIN people ON people.id = coaches.person_id
  INNER JOIN users ON coaches.person_id = users.person_id
  LEFT OUTER JOIN organizations_users ON organizations_users.user_id = users.id
) WHERE rownum <= 25

有什么建议吗？

Answer 1

查询的投影只能有一个给定名称的实例。正如您的WHERE子句所示，您有几个表的列名为ID。因为您选择了*，您的投影将有几个列名为ID.或者，如果不是编译器抛出ORA-00918，它会有。

解决方案非常简单：您必须展开投影以显式选择命名列。然后，您可以省去重复的列，只保留COACHE.ID，或者使用列别名：coaches.id as COACHES_ID。

也许这让你觉得打字太多了，但这是唯一的方法。如果有什么安慰的话，SELECT *在生产代码中被认为是一种糟糕的做法：显式命名的列要安全得多。

Answer 2

内部查询中有多个列的名称相同，因此在外部查询中会引发错误。如果您去掉了外部查询，它应该运行，尽管仍然令人困惑：

SELECT DISTINCT
    coaches.id,
    people.*,
    users.*,
    coaches.*
FROM "COACHES"
    INNER JOIN people ON people.id = coaches.person_id
    INNER JOIN users ON coaches.person_id = users.person_id
    LEFT OUTER JOIN organizations_users ON organizations_users.user_id = users.id
WHERE
    rownum <= 25

准确地指定每个表中所需的字段，而不是全部选择字段，会更好（从可读性和性能两方面来看）。然后，如果您确实需要从不同的表中调用相同内容的两个字段，请使用列别名来区分它们。

Answer 3

在选择对应列可以为null的并集时，也可以看到此错误。

select * from (select D.dept_no, D.nullable_comment
                  from dept D
       union
               select R.dept_no, NULL
                 from redundant_dept R
)

这显然混淆了解析器，解决方案是为始终为null的列分配一个列别名。

select * from (select D.dept_no, D.comment
                  from dept D
       union
               select R.dept_no, NULL "nullable_comment"
                 from redundant_dept R
)

别名不必与相应的列相同，但结果中的列标题是由联合成员中的第一个查询驱动的，因此这可能是一个好的做法。

Answer 4

SELECT  DISTINCT  
        per_all_people_f.EMPLOYEE_NUMBER 
        , MAX(per_all_people_f.LAST_UPDATE_DATE) 
        , per_all_people_f.KNOWN_AS  FULL_NAME
        , to_char(notified_termination_date, 'DD-MM-YYYY') AS termination_date 
        , :FROM_DATE  DATE1
        , :TO_DATE DATE2
--      , D_LEAVING_REASON AS D_LEAVING_REASON
        , CASE substr(substr(hr_all_organization_units_tl.NAME, instr(hr_all_organization_units_tl.NAME, '.') + 1), 1, 1)
        WHEN 'B'  THEN
            'إدارة الاتصالات وتقنية المعلومات'
        WHEN 'C'  THEN
            'إدارة المشاريع'
        WHEN 'D'  THEN
            'الإدارة القانونية'
        WHEN 'E'  THEN
            'إدارة الصحه والسلامة والبيئه'
        WHEN 'F'  THEN
            'إدارة هندسة المكامن والانتاج'
        WHEN 'G'  THEN
            'إدارة الهندسة'
        WHEN 'H'  THEN
            'إدارة العمليات'
        WHEN 'J'  THEN
            'إدارة الحفر وصيانة الآبار'
        WHEN 'K'  THEN
            'إدارة المواد'
        WHEN 'L'  THEN
            'إدارة النقل والخدمات'
        WHEN 'M'  THEN
            'إدارة الاستكشاف'
        WHEN 'N'  THEN
            'إدارة فرع بنغازي'
        WHEN 'P'  THEN
            'إدارة التخطيط'
        WHEN 'R'  THEN
            'إدارة المالية'
        WHEN 'T'  THEN
            'إدارة المراجعه'
        WHEN 'W'  THEN
            'إدارة التدريب والتطوير'
        WHEN 'Y'  THEN
            'إدارة شؤون الموظفين'
            else case  substr(substr(hr_all_organization_units_tl.NAME, instr(hr_all_organization_units_tl.NAME, '.') + 1), 1, 3)
              WHEN 'A11'  THEN
            'لجنة المناقصات'
        WHEN 'A10'  THEN
            'لجنة الادارة'
        WHEN 'A12'  THEN
            'قسم الاعلام '
            end
        END  DEPARTMENT
    , CASE d_leaving_reason 
        WHEN 'Retirement'  THEN
            'التقاعد'
        END 
        LEAVING_REASON1 
FROM    per_all_people_f
LEFT JOIN per_periods_of_service_v ON per_all_people_f.person_id = per_periods_of_service_v.person_id
LEFT JOIN per_assignments_f ON per_all_people_f.EMPLOYEE_NUMBER = per_assignments_f.ASSIGNMENT_NUMBER
LEFT JOIN hr_all_organization_units_tl ON per_assignments_f.ORGANIZATION_ID = hr_all_organization_units_tl.ORGANIZATION_ID
WHERE   notified_termination_date >= TO_DATE(:FROM_DATE,'MM-YYYY') AND notified_termination_date <= TO_DATE(:TO_DATE,'MM-YYYY')
--      AND D_LEAVING_REASON = 'Retirement'
        AND CURRENT_EMPLOYEE_FLAG IS NULL AND employee_number IS NOT NULL
GROUP BY EMPLOYEE_NUMBER,d_leaving_reason,LAST_UPDATE_DATE,KNOWN_AS,notified_termination_date
,:FROM_DATE,:TO_DATE,NAME