This query is generated by a report tool hyperion, we are getting this error ORA-01799: a column may not be outer-joined to a subquery, I am not familiar with the way hyperion created the outer joins, did you see anything?
Thank you!
SELECT al2.sybiden_id, al2.sybiden_first_name, al2.sybiden_mi,
al2.sybiden_last_name, al2.sybiden_ssn, al2.sybiden_birth_date,
al2.sybiden_citz_code, al2.sybiden_pref_first_name,
al1.syraddr_street_line2, al1.syraddr_street_line1,
al1.syraddr_street_line3, al1.syraddr_city, al1.syraddr_stat_code,
al1.syraddr_zip, al1.syraddr_natn_desc, al1.syraddr_phone_formatted,
al6.syraddr_street_line1, al6.syraddr_street_line2,
al6.syraddr_street_line3, al6.syraddr_city, al6.syraddr_stat_code,
al6.syraddr_zip, al6.syraddr_natn_desc, al6.syraddr_phone_formatted,
al4.saradap_styp_code,
saturn_midd.stu_a_util_gen.f_get_email_addr (al2.sybiden_pidm, 'REC1'),
saturn_midd.stu_a_util_gen.f_get_blse_desc_date
(al4.saradap_pidm,
'1',
al4.saradap_term_code_entry,
's'
),
saturn_midd.stu_a_util_gen.f_get_blse_desc_date
(al4.saradap_pidm,
'2',
al4.saradap_term_code_entry,
's'
),
al7.aprehis_empr_name, al8.atvjobc_desc
FROM saturn_midd.syraddr al1, saturn.sarerul al5,
saturn_midd.sybiden al2
LEFT OUTER JOIN saturn_midd.syraddr al6
ON (al2.sybiden_pidm = al6.syraddr_pidm
AND al6.syraddr_atyp_code = 'BU'
)
LEFT OUTER JOIN alumni.aprehis al7
ON ( al7.aprehis_pidm = al2.sybiden_pidm
AND al7.aprehis_seq_no IN (
SELECT MAX (al7.aprehis_seq_no)
FROM alumni.aprehis al7
WHERE (al7.aprehis_pidm = al7.aprehis_pidm
))
OR al7.aprehis_seq_no IS NULL
)
LEFT OUTER JOIN alumni.atvjobc al8
ON (al7.aprehis_jobc_code = al8.atvjobc_code)
, general.goremal al3 RIGHT OUTER JOIN saturn.saradap al4
ON ( al3.goremal_pidm = al4.saradap_pidm
AND al3.goremal_emal_code = 'REC1'
)
WHERE ( al2.sybiden_pidm = al4.saradap_pidm
AND al2.sybiden_pidm = al1.syraddr_pidm
AND al5.sarerul_value = al4.saradap_term_code_entry
)
AND (( al4.saradap_appl_no IN (
SELECT MAX (al4.saradap_appl_no)
FROM saturn.saradap al4
WHERE (( al4.saradap_pidm = al4.saradap_pidm
AND al4.saradap_term_code_entry =
al4.saradap_term_code_entry
)
))
AND al4.saradap_coll_code_1 = 'BL'
AND al1.syraddr_atyp_code = 'BL'
AND al5.sarerul_egrp_code = 'BL'
AND al5.sarerul_function = 'ADM_CURR_TERM'
AND al4.saradap_camp_code = 'BL'
)
)