More SQL Assistance

I am running the script listed below. I am using the MAX function at the end of the script to pull the latest date from a table. My question is, how do I get the data from that table to appear in my output since it is not being called in the first SELECT/FROM/WHERE and still get the data to pull distinct and not multiple records of a user ID.

Code:

SELECT DISTINCT (sfrstcr_pidm)PIDM,
substr(frkiden.f_get_spriden_id(sfrstcr_pidm),1,9) ID,
substr(frkiden.f_get_spriden_first_name(sfrstcr_pidm),1,15) FIRST,
substr(frkiden.f_get_spriden_last_name(sfrstcr_pidm),1,60) LAST,
sgbstdn_resd_code,sprhold_reason

FROM

sfrstcr,sprhold,sovclas,sgbstdn

WHERE

sfrstcr_pidm = sprhold_pidm AND

sfrstcr_pidm = sovclas_pidm AND

sfrstcr_pidm = sgbstdn_pidm  AND

sfrstcr_term_code = sovclas_term_code AND

sfrstcr_term_code = '201360' AND

sfrstcr_rsts_code IN ('RE','RL','RW') AND

sovclas_clas_code IN ('NF','FR') AND

sprhold_hldd_code = 'IM' AND

sprhold_from_date <= '28-OCT-2013' AND

sprhold_to_date >= '28-OCT-2013'AND


sfrstcr_pidm IN
    (SELECT DISTINCT(a.sfrthst_pidm)
    FROM sfrthst a
    WHERE a.sfrthst_tmst_code = 'FT'
        AND a.sfrthst_tmst_date =
      (SELECT Max(b.sfrthst_tmst_date)
      FROM sfrthst b
      WHERE b.sfrthst_pidm = a.sfrthst_pidm))

 

I was able to figure out my above question, although I am not sure I did it the best way when put up against a SQL pro.

I have another question. How can you use a MAX function while also using a LEFT JOIN in a script?

When I run the code below, I receive the following error: ORA-01799: a column may not be outer-joined to a subquery

Don't know if this will help but here is additional information:

- Oracle version: 11.2.0.2.0

- I'm trying get people who are listed on the table SGRSPRT for the term 201410. I want their latest standing listed on SHRTTRM. The reason I am doing a JOIN is because some people do not have a standing but I still want to pull them. The reason I am using a MAX is because a people can have more than one term standing so I want to pull the latest one.

- My expected output would look something like this: http://imgur.com/lPE1nNg

Code:

SELECT (sgrsprt_pidm)                                              PIDM, 
       Substr(frkiden.F_get_spriden_id(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'

 

That actually worked except for the fact 2 records are not pulled because the records have no standing and this is why I was using the JOIN because I wanted them even if there was no standing. Any way to alleviate that issue? My question is, how does this work especially since you didn't call the shrttrm table in the FROM clause. Sorry, I am pretty novice at the whole SQL scripting now but I am making steps. Is there any way to put that MAX function in the JOIN or pull those records even if they do not have a standing?

 

OK, I actually rewrote it and it pulls the correct data. I removed the JOIN and just used the MAX. I have no idea how this works but it pulls the correct data.

Code:

SELECT (SGRSPRT_pidm)PIDM,
substr(frkiden.f_get_spriden_id(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,sgrsprt_actc_code,

      (SELECT a. shrttrm_astd_code_end_of_term
    FROM shrttrm a
    WHERE a.shrttrm_pidm = sgrsprt_pidm
     AND
    a.shrttrm_term_code =
      (SELECT Max(b.shrttrm_term_code)
      FROM shrttrm b
      WHERE b.shrttrm_pidm = a.shrttrm_pidm)) Last_Academic_Standing


FROM SGRSPRT

WHERE sgrsprt_term_code = '201410';