具有多个JOINS Oracle的多个子查询

收藏

我有一个带有多个联接和多个子查询的大型ORACLE SQL文件。我在加入两个子查询时遇到问题。我尝试了许多不同的方法,但是下面是我得到的最接近的方法。麻烦的是与多个子查询(地址生效日期和员工生效日期)连接的第一部分。我收到的错误消息是ORA-00907:缺少右括号00907。00000-“缺少右括号”。我找不到右括号的位置,我怀疑它不止于此或其他语法类型。我在合并时遇到问题的两个表是EMPLOYEE_EFF_DATE表和ADDRESS_EFF_DATE表。这些表中有一个EFF_DATE字段,对于这两个表,我都需要提取具有最近生效日期的记录(按SSN字段)。我相信我已经用ROW_NUMBER()OVER(PARTITION方法)解决了这一问题,但是如果存在我绝对乐于接受建议的更有效或更简便的方法。

/*Subscriber Code*/
    eed.P_COMPANY_ID_I as "Client Company ID"
    ,cs.REAL_SSN as "Employee SSN"
    ,cs.REAL_SSN as "Member SSN"
    ,'Subscriber' as "Person Type"
    ,eed.LAST_NAME as "Last Name"
    ,eed.FIRST_NAME as "First Name"
    ,eed.BIRTHDATE as "Date of Birth"
    ,eed.SEX as "Gender"
    ,aed.Address_1 as "Address 1"
    ,aed.Address_2 as "Address 2"
    ,aed.City
    ,aed.State
    ,aed.Zip
    ,aed.Country as "Country Code"
    ,aed.Telephone as "Employee Home Phone"
    ,eed.EMAIL_ADDRESS as "Employee Email Address"

    ,CASE 
        WHEN eed.controlled_group_status = 'L' 
        OR eed.controlled_group_status = 'P' 
        then eed.EFF_DATE
        ELSE NULL
    END
    as "Date Last Actively At Work"    
    /*Remove line below*/
    ,eed.CONTROLLED_GROUP_STATUS
    ,CASE  
        WHEN eed.controlled_group_status = 'L' 
        OR eed.controlled_group_status = 'P' 
        then 'LEAVE'
        ELSE NULL
    END
    as "Leave Status"
    ,CASE 
        WHEN eed.controlled_group_status = 'L' 
        OR eed.controlled_group_status = 'P' 
        then eed.EFF_DATE + 1   
        ELSE NULL
    END
    as "Leave Begin Date"
    ,eed.LATEST_HIRE_DATE as "Employee Date of Hire"
    ,eed.LAST_TERM_DATE as "Employee Date of Termination"
    ,mcemd.RATE_1 as "Employee Salary"
    ,ele.LIFE_EVENT_ID as "Life Event ID"
    ,ele.LIFE_EVENT_DATE as "Loss of Coverage Date"



FROM

/*Employee_Eff_Date*/
(SELECT *
FROM
(SELECT *
FROM
     (SELECT eed1.*,
             ROW_NUMBER() OVER (PARTITION BY eed1.SSN ORDER BY EFF_DATE DESC) as seqnum
      FROM EMPLOYEE_EFF_DATE eed1)
WHERE seqnum = 1) eed)

JOIN    
/*Address_Eff_Date*/
(SELECT *
FROM
    (SELECT *
    FROM
     (SELECT aed1.*,
             ROW_NUMBER() OVER (PARTITION BY aed1.SSN ORDER BY EFF_DATE DESC) as seqnum
      FROM ADDRESS_EFF_DATE aed1
     ) aed1
     ON aed1.SSN = eed.SSN
WHERE aed1.seqnum = 1) aed) 

INNER JOIN COMPANY_EMPLOYMENT_DATA ced
    ON eed.SSN = ced.SSN
INNER JOIN MV_COMB_EMP_MAX_DTS mcemd
    ON eed.SSN = mcemd.SSN
INNER JOIN EMPLOYEE_LIFE_EVENTS ele
    ON ele.SSN = eed.SSN


AND eed.P_COMPANY_ID_I = 1234

/*Address_Eff_Date qualifying statement*/
AND aed.ADDRESS_KEY = 0

/*EMPLOYEE_LIFE_EVENTS qualifying statement*/
/*Below line indicates the Life Event Dates (set to the past week)*/
AND ele.LIFE_EVENT_DATE >= sysdate-7
AND ele.LIFE_EVENT_DATE <= sysdate
回复
  • 我想你真的想要这个。请注意,您的查询嵌套级别比实际需要的多,并且我删除了多余的嵌套:

    /*Employee_Eff_Date*/
    (SELECT *
    FROM
         (SELECT eed1.*,
                 ROW_NUMBER() OVER (PARTITION BY eed1.SSN ORDER BY EFF_DATE DESC) as seqnum
          FROM EMPLOYEE_EFF_DATE eed1)
    WHERE seqnum = 1) eed
    JOIN    
    /*Address_Eff_Date*/
    (SELECT *
     FROM
         (SELECT aed1.*,
                 ROW_NUMBER() OVER (PARTITION BY aed1.SSN ORDER BY EFF_DATE DESC) as seqnum
          FROM ADDRESS_EFF_DATE aed1
         ) aed1
         WHERE aed1.seqnum = 1) aed ON aed.SSN = eed.SSN