We help IT Professionals succeed at work.

IF.. ELSEIF               Getting a SQL error for a function

mytfein
mytfein asked
on
307 Views
Last Modified: 2014-08-26
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;

Open in new window



2014-08-26-function.bmp
Comment
Watch Question

CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:
Think I found the differences.

give me a minute
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
Hi SlightWV,

oh, thx so much for catching the syntax....

it compiled.... but if i google "ELSEIF Oracle"

i do find documentions with the letter "E" :   http://docs.oracle.com/cd/E12032_01/doc/epm.921/html_techref/calc/elseif.htm

(I used Microsoft Access a little more, and that's where i got the ELSEIF syntax in my head)

Now will look into your alternate idea... thx so much ....

sandra



I
Sean StuberDatabase Developer & Administrator
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2012
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
Hi Slightwv,

Thx for explaining why the link i posted is not genuine oracle syntax

Sandra
Sean StuberDatabase Developer & Administrator
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2012

Commented:
I'm sdstuber, not slightwv - but you're welcome!
Sean StuberDatabase Developer & Administrator
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2012
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
Hi sdstuber,

yes, so sorry, THANK YOU for the syntax explanation.. i noticed i addressed you incorrectly a few minutes after hitting the submit button... thank you....

yes, i noticed that parm_attrb not part of SlightWV's code

so i was attempting to merge both of your ideas into one....

will get back to you guys... i see you posted revised code, too... so will assimilate this too...

I want to compliment you guys on your approach...  brilliant....

thx... will write back soon, sandra

Author

Commented:
Hi Gentlmen,

So using your ideas this is what i came up with:

I will have 4 values  for PARM_flag:
ACADEMIC_YEAR  - filter by Academic Year
MATRIC_YEAR      -  filter by Matriculated Year

Since students can be in one of 4 year journey of medical school, we have a year attribute and might want totals by the journey year.

ACADEMIC_YEAR_ATTRB - filter by Academic Year and attribute
MATRIC_YEAR_ATTRB  - (new value as it was handled by final ELSE of original code)
                                            filter by Matric Year and attribute


PARM_attrib will always be filled in with:
'M1' - Medical school year 1
'M2 - Medical school year 2
'M3 - Medical school year 3
'M4 - Medical school year 4

'XX'  - when not filtering on on Attribute



So this is revised WHERE clause snippet
==================================


AND
    (
     (parm_flag IN ('MATRIC_YEAR', ‘MATRIC_YEAR_ATTRB’)
                                                       AND a.year_matriculated = parm_year)
              OR
    (parm_flag IN ('ACADEMIC_YEAR', 'ACADEMIC_YEAR_ATTRB')
                                                       AND a.academic_year = parm_year)
    )
AND
   (
    (parm_flag IN ('MATRIC_YEAR_ATTRB', ‘ACADEMIC_YEAR_ATTRB’)
                                                     AND a.student_classification = parm_attribute)
             OR
     (Parm_flag = ‘XX’)
    )

thx, sandra

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.