• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 471
  • Last Modified:

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

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
mytfein
Asked:
mytfein
  • 3
  • 2
5 Solutions
 
awking00Commented:
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
 
slightwv (䄆 Netminder) Commented:
>>THEN 'June' END

This END kills the outer CASE.
0
 
awking00Commented:
...
'MDMP'   THEN 'MDMP' END) IN ('MDMP','MDPH') AND SUBSTR(Academic_Period_Matriculated,5,2) = '10' THEN 'June' END => NOT NEEDED
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
awking00Commented:
Beat me again :-)
0
 
slightwv (䄆 Netminder) Commented:
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
 
mytfeinAuthor Commented:
tx so much everyone,

removed the 'END'

it works now...

tx again, s
0

Featured Post

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now