IF.. ELSEIF Getting a SQL error for a function

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
mytfeinAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

slightwv (䄆 Netminder) Commented:
It is ELSIF.

No E.

That said, I think this can be simplified.

I'm still looking but can you point out the differences in each individual select based on the parameter?
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
slightwv (䄆 Netminder) Commented:
Think I found the differences.

give me a minute
0
slightwv (䄆 Netminder) Commented:
See if this will work for you.

It might be a little off but it should at least show what I'm doing.

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;

       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
			(
				(PARM_Flag in ('ACADEMIC_YEAR','ACADEMIC_YEAR_ATTRB') and A.ACADEMIC_YEAR = PARM_YEAR)
				or
				(PARM_Flag not in ('ACADEMIC_YEAR','ACADEMIC_YEAR_ATTRB') 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;

return lngCount;
end DMC_COUNT_STATUS_BY_COLLG_YEAR;

Open in new window

0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

mytfeinAuthor 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
0
sdstuberCommented:
the link in the previous post is for hyperion epm documentation,  not oracle database sql


also note the parm_attribute check is not included in slightwv's example
0
mytfeinAuthor Commented:
Hi Slightwv,

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

Sandra
0
sdstuberCommented:
I'm sdstuber, not slightwv - but you're welcome!
0
sdstuberCommented:
I think this captures all of the conditions while also simplifying the query

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;

    SELECT COUNT(student_status)
      INTO lngcount
      FROM academic_study a
     WHERE a.college = parm_college
       AND ((parm_flag = 'MATRIC_YEAR' AND a.year_matriculated = parm_year)
         OR (parm_flag IN ('ACADEMIC_YEAR', 'ACADEMIC_YEAR_ATTRB') AND a.academic_year = parm_year)
         OR (parm_flag = 'ACADEMIC_YEAR_ATTRB' AND a.student_classification = parm_attribute)
         OR (parm_flag NOT IN ('MATRIC_YEAR', 'ACADEMIC_YEAR', 'ACADEMIC_YEAR_ATTRB')
                       AND a.year_matriculated = parm_year
                       AND a.student_classification = parm_attribute)
       )
       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
                               );

    RETURN lngcount;
END dmc_count_status_by_collg_year;

Open in new window

0
mytfeinAuthor 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
0
mytfeinAuthor 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
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.