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'
Can you move the subquery to be part of the WHERE clause? 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 WHERE sgrsprt_actc_code IS NOT NULL AND sgrsprt_term_code = '201410' AND (shrttrm_term_code = (SELECT Max(shrttrm_term_code) FROM shrttrm WHERE shrttrm_term_code < '201410') or shrttrm_term_code is null)
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?
The "or shrttrm_term_code is null" should have made it include the entries with no matching record in the shrttrm table, unless that's something that is different between MSSQL and Oracle. If you run it without the subquery what value is returned in the shrttrm_term_code column? This may not be the technically correct answer, but think of the subquery as something that is preprocessed and the returned value(s) are put into place when the final query is run, so it's treated separately from the rest of the query and therefore doesn't need to be in the FROM clause.
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';