Solved

Oracle SQL:  getting syntax error on sql ... snippet below...

Posted on 2013-06-25
6
452 Views
Last Modified: 2013-06-25
Hi EE,

can you pls help with deciperhing the syntax error below...

am creating a complicated sql...

  it's bec. a student's attribute can reside in either 2 fields

       student_attribute_1
       student_attribute_2

 so am creating a field called calc_attribute to hold the attribute

bec want to create an excel file, to run a Microsoft word mail merge
so want the attribute in one place

i do this using a case when statement....

====

now if the calc_attrib field = MDMP  or MDPH then it's a dual degree medical student student
    and i want to create new fields:
           MD_Matric_Month,
           MD_ Matric_Year
           MD_ADG

i need to derive MD_Matric_Month  from the field: ACADEMIC_PERIOD_MATRICULATED
       and calculate as a named month

term:  201310    10 =  June
            201320   20=   August
            201330   30 =  January

so i am having trouble with the case when statement that are assigning these month values

the case where it's trying to calculate 'August' is giving an error:

"FROM KEYWORD NOT FOUND WHERE EXPECTED"

tx so much for your help, sandra

below is a code snippet:

SELECT  
        ACADEMIC_PERIOD
        ,COLLEGE
        ,PROGRAM          
        ,ID
        ,NAME
        ,STUDENT_ATTRIBUTE_1
        ,STUDENT_ATTRIBUTE_2        
         ,ACADEMIC_PERIOD_MATRICULATED
        ,CASE WHEN   STUDENT_ATTRIBUTE_1 = 'SD' OR STUDENT_ATTRIBUTE_2 = 'SD'  THEN 'SD'
             WHEN   STUDENT_ATTRIBUTE_1 = 'TR' OR STUDENT_ATTRIBUTE_2 = 'TR'  THEN 'TR'
             WHEN   STUDENT_ATTRIBUTE_1 = 'MDPH' OR STUDENT_ATTRIBUTE_2 = 'MDPH'   THEN 'MDPH'
             WHEN   STUDENT_ATTRIBUTE_1 = 'MDMP' OR STUDENT_ATTRIBUTE_2 = 'MDMP'   THEN 'MDMP' END
           AS Calc_Attrib

     
       ,CASE WHEN (CASE              
                        WHEN   STUDENT_ATTRIBUTE_1 = 'MDPH' OR STUDENT_ATTRIBUTE_2 = 'MDPH'   THEN 'MDPH'
                        WHEN   STUDENT_ATTRIBUTE_1 = 'MDMP' OR STUDENT_ATTRIBUTE_2 = 'MDMP'   THEN 'MDMP' END) IN ('MDMP','MDPH')
                                AND SUBSTR(Academic_Period_Matriculated,5,2) = '10' THEN 'June' END    
                               
             WHEN (CASE                            
                        WHEN   STUDENT_ATTRIBUTE_1 = 'MDPH' OR STUDENT_ATTRIBUTE_2 = 'MDPH'   THEN 'MDPH'
                        WHEN   STUDENT_ATTRIBUTE_1 = 'MDMP' OR STUDENT_ATTRIBUTE_2 = 'MDMP'   THEN 'MDMP' END) IN ('MDMP','MDPH')
                                AND SUBSTR(Academic_Period_Matriculated,5,2) = '20' THEN 'August' END                                
                               
                               
           as Calc_DualMatricMonth
0
Comment
Question by:mytfein
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
6 Comments
 
LVL 32

Assisted Solution

by:awking00
awking00 earned 300 total points
ID: 39275160
You have two case statements within a case statement. Each of the inner case statements include an END keyword, but you also have two END keywords for the single outer case statement.
0
 
LVL 77

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 200 total points
ID: 39275166
>>THEN 'June' END

This END kills the outer CASE.
0
 
LVL 32

Assisted Solution

by:awking00
awking00 earned 300 total points
ID: 39275180
...
'MDMP'   THEN 'MDMP' END) IN ('MDMP','MDPH') AND SUBSTR(Academic_Period_Matriculated,5,2) = '10' THEN 'June' END => NOT NEEDED
0
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

 
LVL 32

Assisted Solution

by:awking00
awking00 earned 300 total points
ID: 39275187
Beat me again :-)
0
 
LVL 77

Accepted Solution

by:
slightwv (䄆 Netminder) earned 200 total points
ID: 39275192
When nesting code like this, I always like to line things up.  It's easier to locate mistakes that way:

SELECT  
       CASE WHEN (CASE              
                        WHEN   STUDENT_ATTRIBUTE_1 = 'MDPH' OR STUDENT_ATTRIBUTE_2 = 'MDPH'  
                           THEN 'MDPH'
                        WHEN   STUDENT_ATTRIBUTE_1 = 'MDMP' OR STUDENT_ATTRIBUTE_2 = 'MDMP'
                           THEN 'MDMP'
                  END
                 ) IN ('MDMP','MDPH')
                 AND SUBSTR(Academic_Period_Matriculated,5,2) = '10'
       THEN 'June'
      END
             WHEN (CASE                            
                        WHEN   STUDENT_ATTRIBUTE_1 = 'MDPH' OR STUDENT_ATTRIBUTE_2 = 'MDPH'  
                            THEN 'MDPH'
                        WHEN   STUDENT_ATTRIBUTE_1 = 'MDMP' OR STUDENT_ATTRIBUTE_2 = 'MDMP'  
                            THEN 'MDMP' END
                  ) IN ('MDMP','MDPH')
                  AND SUBSTR(Academic_Period_Matriculated,5,2) = '20'
       THEN 'August'
       END                                
       as Calc_DualMatricMonth
0
 

Author Comment

by:mytfein
ID: 39275341
tx so much everyone,

removed the 'END'

it works now...

tx again, s
0

Featured Post

Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

Question has a verified solution.

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

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…
How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.
Suggested Courses

617 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