Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

IF.. ELSEIF               Getting a SQL error for a function

Posted on 2014-08-26
11
Medium Priority
?
293 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
0
Comment
Question by:mytfein
  • 4
  • 3
  • 3
10 Comments
 
LVL 78

Accepted Solution

by:
slightwv (䄆 Netminder) earned 1000 total points
ID: 40286125
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
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40286135
Think I found the differences.

give me a minute
0
 
LVL 78

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 1000 total points
ID: 40286153
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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 

Author Comment

by:mytfein
ID: 40286217
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
 
LVL 74

Assisted Solution

by:sdstuber
sdstuber earned 1000 total points
ID: 40286233
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
 

Author Comment

by:mytfein
ID: 40286240
Hi Slightwv,

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

Sandra
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 40286247
I'm sdstuber, not slightwv - but you're welcome!
0
 
LVL 74

Assisted Solution

by:sdstuber
sdstuber earned 1000 total points
ID: 40286250
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
 

Author Comment

by:mytfein
ID: 40286261
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
 

Author Comment

by:mytfein
ID: 40286312
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

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.
Suggested Courses
Course of the Month14 days, 12 hours left to enroll

577 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question