Solved

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

Posted on 2013-06-25
6
442 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
  • 3
  • 2
6 Comments
 
LVL 31

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 76

Assisted Solution

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

This END kills the outer CASE.
0
 
LVL 31

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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 31

Assisted Solution

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

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
Via a live example, show how to take different types of Oracle backups using RMAN.

746 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now