mytfein
asked on
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_MATRICULAT ED
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_MATRICULA TED
,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_Mat riculated, 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_Mat riculated, 5,2) = '20' THEN 'August' END
as Calc_DualMatricMonth
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_MATRICULAT
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_MATRICULA
,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_Mat
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_Mat
as Calc_DualMatricMonth
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
removed the 'END'
it works now...
tx again, s