Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

ORA-01799: a column may not be outer-joined

651180Sep 14 2009 — edited Sep 14 2009
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'
        )
       )
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Oct 12 2009
Added on Sep 14 2009
4 comments
2,324 views