January 25, 2007, 04:16 PM
<Nate Conn>SELECT spriden_id ID,
spriden_first_name first_name,
spriden_mi middle_name,
spriden_last_name last_name,
spbpers_birth_date birth_date,
spbpers_ethn_code ethn_code,
f_student_get_desc ('STVETHN', spbpers_ethn_code, 30) ethn_desc,
aac003_f.f_get_grade (spriden_pidm, 'ENGL', '110') engl110,
aac003_f.f_get_grade_term (spriden_pidm, 'ENGL', '110') engl110_term,
aac003_f.f_get_grade (spriden_pidm, 'ENGL', '111') engl111,
aac003_f.f_get_grade_term (spriden_pidm, 'ENGL', '111') engl111_term,
aac003_f.f_get_grade (spriden_pidm, 'COMM', '211') comm211,
aac003_f.f_get_grade_term (spriden_pidm, 'COMM', '211') comm211_term,
aac003_f.f_get_grade (spriden_pidm, 'COMM', '225') comm225,
aac003_f.f_get_grade_term (spriden_pidm, 'COMM', '225') comm225_term,
aac003_f.f_get_grade (spriden_pidm, 'EDUC', '115') educ115,
aac003_f.f_get_grade_term (spriden_pidm, 'EDUC', '115') educ115_term,
aac003_f.f_get_grade (spriden_pidm, 'EDUC', '223') educ223,
aac003_f.f_get_grade_term (spriden_pidm, 'EDUC', '223') educ223_term,
aac003_f.f_get_grade (spriden_pidm, 'EDUC', '224') educ224,
aac003_f.f_get_grade_term (spriden_pidm, 'EDUC', '224') educ224_term,
aac003_f.f_get_grade (spriden_pidm, 'EDUC', '320') educ320,
aac003_f.f_get_grade_term (spriden_pidm, 'EDUC', '320') educ320_term
FROM sgbstdn,
spriden,
spbpers
WHERE sgbstdn_term_code_eff = (SELECT MAX (sgbstdn_term_code_eff)
FROM sgbstdn
WHERE sgbstdn_pidm = spriden_pidm)
AND spriden_change_ind IS NULL
AND (sgbstdn_majr_code_conc_1_3 = 'TEDO'
OR sgbstdn_majr_code_conc_1 = 'TEDO'
OR sgbstdn_majr_code_conc_1_2 = 'TEDO'
OR sgbstdn_majr_code_conc_2 = 'TEDO'
OR sgbstdn_majr_code_conc_2_2 = 'TEDO'
OR sgbstdn_majr_code_conc_2_3 = 'TEDO'
OR sgbstdn_majr_code_conc_121 = 'TEDO'
OR sgbstdn_majr_code_conc_122 = 'TEDO'
OR sgbstdn_majr_code_conc_123 = 'TEDO'
OR sgbstdn_majr_code_conc_221 = 'TEDO'
OR sgbstdn_majr_code_conc_222 = 'TEDO'
OR sgbstdn_majr_code_conc_223 = 'TEDO')
AND sgbstdn_pidm IN (
SELECT sfrstcr_pidm
FROM sfrstcr
WHERE sfrstcr_term_code = '&ug_term'
AND (sfrstcr_rsts_code = 'AU'
OR sfrstcr_rsts_code IN (SELECT stvrsts_code
FROM stvrsts
WHERE stvrsts_incl_sect_enrl = 'Y')))
AND spriden_pidm = sgbstdn_pidm
AND spbpers_pidm = sgbstdn_pidm
ORDER BY spriden_last_name, spriden_first_name, spriden_mi, spriden_id;
Here's the AAC003_F.f_get_grade function code
Note the aac003_f.f_get_grade_term is similar just returning the term:
CREATE OR REPLACE FUNCTION AAC003_F.f_get_grade (
p_pidm IN NUMBER,
p_subj IN VARCHAR2,
p_crse IN VARCHAR2)
RETURN VARCHAR2
IS
v_grade VARCHAR2 (6);
BEGIN
SELECT shrtckg_grde_code_final
INTO v_grade
FROM shrtckg,
shrtckn
WHERE shrtckn_subj_code = p_subj
AND shrtckn_crse_numb = p_crse
AND shrtckg_credit_hours > 0
AND shrtckg_gmod_code = 'N'
AND shrtckn_pidm = p_pidm
AND (shrtckn_repeat_course_ind IS NULL
OR shrtckn_repeat_course_ind = 'I')
AND shrtckg_seq_no =
(SELECT MAX (shrtckg_seq_no)
FROM shrtckg
WHERE shrtckg_pidm = p_pidm
AND shrtckg_term_code = shrtckn_term_code
AND shrtckg_tckn_seq_no = shrtckn_seq_no)
AND shrtckg_term_code = shrtckn_term_code
AND shrtckg_pidm = shrtckn_pidm
AND shrtckg_tckn_seq_no = shrtckn_seq_no;
*****Note I can take a few(9) of those functions out of the select and it will work.
Thanks for the help.