troubleshooting Question

IF.. ELSEIF               Getting a SQL error for a function

Avatar of mytfein
mytfein asked on
Oracle Database
10 Comments4 Solutions310 ViewsLast Modified:
Hi EE,

Want to set this function up using

IF
ELSEIF
ELSEIF
ELSE
END IF

Don't know why it's not compiling...

Can you please have a look....

function   and screen shot with error are below...

thx, sandra

create or replace function DMC_COUNT_STATUS_BY_COLLG_YEAR
         (PARM_College         varchar2
         ,PARM_Year            varchar2
         ,PARM_Status          varchar2
         ,PARM_TermCutoff      varchar2
         ,PARM_Attribute       varchar2
         ,PARM_flag         varchar2)
  
return number is lngCount number;

  
begin
  lngCount := 0;

If PARM_Flag = 'MATRIC_YEAR' THEN
       SELECT COUNT(A1.STUDENT_STATUS)
              INTO lngCount
       FROM
           (
             select COLLEGE
                 ,  ACADEMIC_YEAR
                 ,  ACADEMIC_PERIOD
                 ,  NAME
                 ,  ID
                 ,   student_status
                 , ODSMGR.DMC_MAX_TERM_FOR_STUDENTS_PRGM
                              (A.PERSON_UID  
                             , PARM_TermCutoff
                             , 'N' 
                             , A.PROGRAM) as Calc_MaxTerm
           
            from ACADEMIC_STUDY A
            WHERE
                  A.COLLEGE = PARM_College
                        AND
                  A.YEAR_MATRICULATED = PARM_Year
                        AND         
                  A.STUDENT_STATUS = PARM_STATUS
                        AND
                  A.ACADEMIC_PERIOD =                                                                                          ODSMGR.DMC_MAX_TERM_FOR_STUDENTS_PRGM

                   (A.PERSON_UID  
                  , PARM_TermCutoff
                  , 'N' 
                  , A.PROGRAM)
  
            )  A1;

ELSEIf PARM_Flag = 'ACADEMIC_YEAR' THEN

       SELECT COUNT(A1.STUDENT_STATUS)
              INTO lngCount
       FROM
           (
             select COLLEGE
                 ,  ACADEMIC_YEAR
                 ,  ACADEMIC_PERIOD
                 ,  NAME
                 ,  ID
                 ,   student_status
                 , ODSMGR.DMC_MAX_TERM_FOR_STUDENTS_PRGM
                              (A.PERSON_UID  
                             , PARM_TermCutoff
                             , 'N' 
                             , A.PROGRAM) as Calc_MaxTerm
           
            from ACADEMIC_STUDY A
            WHERE
                  A.COLLEGE = PARM_College
                        AND
                  A.ACADEMIC_YEAR = PARM_Year
                        AND         
                  A.STUDENT_STATUS = PARM_STATUS
                        AND
                  A.ACADEMIC_PERIOD =                                                                                          ODSMGR.DMC_MAX_TERM_FOR_STUDENTS_PRGM

                   (A.PERSON_UID  
                  , PARM_TermCutoff
                  , 'N' 
                  , A.PROGRAM)
  
            )  A1;

ELSEIf PARM_Flag = 'ACADEMIC_YEAR_ATTRB' THEN

           SELECT COUNT(A1.STUDENT_STATUS)
              INTO lngCount
       FROM
           (
             select COLLEGE
                 ,  ACADEMIC_YEAR
                 ,  ACADEMIC_PERIOD
                 ,  NAME
                 ,  ID
                 ,   student_status
                 ,   student_CLASSIFICATION

                 , ODSMGR.DMC_MAX_TERM_FOR_STUDENTS_PRGM
                              (A.PERSON_UID  
                             , PARM_TermCutoff
                             , 'N' 
                             , A.PROGRAM) as Calc_MaxTerm
           
            from ACADEMIC_STUDY A
            WHERE
                  A.COLLEGE = PARM_College
                        AND
                  A.ACADEMIC_YEAR = PARM_Year
                        AND         
                  A.STUDENT_STATUS = PARM_STATUS
                        AND
                  A.ACADEMIC_PERIOD =                                                                                          ODSMGR.DMC_MAX_TERM_FOR_STUDENTS_PRGM

                   (A.PERSON_UID  
                  , PARM_TermCutoff
                  , 'N' 
                  , A.PROGRAM)
                       AND
                  A.STUDENT_CLASSIFICATION = PARM_Attribute
  
            )  A1;




ELSE
           SELECT COUNT(A1.STUDENT_STATUS)
              INTO lngCount
       FROM
           (
             select COLLEGE
                 ,  ACADEMIC_YEAR
                 ,  ACADEMIC_PERIOD
                 ,  NAME
                 ,  ID
                 ,   student_status
                 ,   student_CLASSIFICATION

                 , ODSMGR.DMC_MAX_TERM_FOR_STUDENTS_PRGM
                              (A.PERSON_UID  
                             , PARM_TermCutoff
                             , 'N' 
                             , A.PROGRAM) as Calc_MaxTerm
           
            from ACADEMIC_STUDY A
            WHERE
                  A.COLLEGE = PARM_College
                        AND
                  A.YEAR_MATRICULATED = PARM_Year
                        AND         
                  A.STUDENT_STATUS = PARM_STATUS
                        AND
                  A.ACADEMIC_PERIOD =                                                                                          ODSMGR.DMC_MAX_TERM_FOR_STUDENTS_PRGM

                   (A.PERSON_UID  
                  , PARM_TermCutoff
                  , 'N' 
                  , A.PROGRAM)
                       AND
                  A.STUDENT_CLASSIFICATION = PARM_Attribute
  
            )  A1;


END if;
        
return lngCount;
end DMC_COUNT_STATUS_BY_COLLG_YEAR;


2014-08-26-function.bmp
ASKER CERTIFIED SOLUTION
Join our community to see this answer!
Unlock 4 Answers and 10 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 4 Answers and 10 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros