CODEWITHSUNDEEP
sqloracle-databasejoinsubquery
Chuck0185
Chuck0185

Reputation: 531

Multiple Sub Queries with Multiple JOINS Oracle

I have a large ORACLE SQL file with multi joins and multiple sub queries. I am having issues joining two of the sub queries. I have tried many different methods but the closest that I have gotten is below. The piece that is giving me trouble is the first portion of the join with the multiple subqueries (the Address Eff Date and the Employee Eff Date. The error message that I am receiving is ORA-00907: missing right parenthesis 00907. 00000 - "missing right parenthesis". I can't find where the right parenthesis is missing and I suspect it is more than that or some other type of syntax issue. The two tables that I have had trouble joinging are the EMPLOYEE_EFF_DATE table and the ADDRESS_EFF_DATE table. Both of these tables have an EFF_DATE field, and for both tables I need to pull the record with the most recent effective date (by SSN field). I believe I have accounted for that with the ROW_NUMBER() OVER (PARTITION method but if there is a more efficient or easier way to to this I am absolutely open to suggestions. 

/*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


WHERE 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

Upvotes: 0

Views: 438

Answers (1)

Nick
Nick

Reputation: 147286

I think you actually want this. Note you have more level of query nesting than you actually need and I have removed the extra ones:

/*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) 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

Upvotes: 1

Related Questions