Results 1 to 2 of 2
  1. #1
    Join Date
    Jan 2014
    Posts
    2

    Unanswered: Left Join With a Max

    Whenever I attempt to run the code below I receive the following error:

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

    Does anybody have any solutions that might resolve this issue?

    Code:
    SELECT (sgrsprt_pidm)PIDM,
    substr(frkiden.f_get_spriden_id(a.sgrsprt_pidm),1,9) ID,
    substr(frkiden.f_get_spriden_first_name(sgrsprt_pidm),1,15) FIRST,
    substr(frkiden.f_get_spriden_last_name(sgrsprt_pidm),1,60) LAST,
    shrttrm_astd_code_end_of_term
    
    FROM
    
    sgrsprt
    
    LEFT JOIN shrttrm ON sgrsprt_pidm = shrttrm_pidm AND shrttrm_term_code
         = (select max(shrttrm_term_code)
              from shrttrm
             where shrttrm_term_code < '201410')
    
    
    WHERE
    
    sgrsprt_actc_code IS NOT NULL AND
    
    sgrsprt_term_code = '201410'
    Last edited by Jakz34; 01-09-14 at 17:29.

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Code:
    01799, 00000, "a column may not be outer-joined to a subquery"
    //  *Cause: <expression>(+) <relop> (<subquery>) is not allowed.
    //  *Action: Either remove the (+) or make a view out of the subquery.
    //           In V6 and before, the (+) was just ignored in this case.
    Code:
    SELECT ( sgrsprt_pidm )                                              PIDM, 
           Substr(frkiden.F_get_spriden_id(a.sgrsprt_pidm), 1, 9)        ID, 
           Substr(frkiden.F_get_spriden_first_name(sgrsprt_pidm), 1, 15) FIRST, 
           Substr(frkiden.F_get_spriden_last_name(sgrsprt_pidm), 1, 60)  LAST, 
           shrttrm_astd_code_end_of_term 
    FROM   sgrsprt 
           left join shrttrm 
                  ON sgrsprt_pidm = shrttrm_pidm 
                     AND shrttrm_term_code = (SELECT Max(shrttrm_term_code) 
                                              FROM   shrttrm 
                                              WHERE  shrttrm_term_code < '201410') 
    WHERE  sgrsprt_actc_code IS NOT NULL 
           AND sgrsprt_term_code = '201410'
    Last edited by anacedent; 01-09-14 at 23:16.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •