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

FROM keyword not found where expected

I'm getting an error message "FROM keyword not found where expected" when I run the following query. What am I doing wrong?

 SELECT rep_start_year,
         university_code,
         EDU_ACT_TYPE_CODE,
         COUNT(*) AS EDU_ACT_TYPE_count,
         EDU_ACT_TYPE_EN_DESC
     


    FROM (SELECT enrol.Rep_Start_Year,  uni.university_code, 
    
                 CASE
                     WHEN EDU_ACT_TYPE_CODE in ('11','19','20','21','30','31','40','41') THEN 'Other'
                     ELSE EDU_ACT_TYPE_CODE
                 END
                 EDU_ACT_TYPE_CODE, 
                 
                 eduacttyp.EDU_ACT_TYPE_EN_DESC  
    
                     
                     
                 uni.university_id,
                 (CASE
                      WHEN uni.university_code = 'DOMC' THEN 'CARL'
                      WHEN uni.university_code = 'NOSM-LAKE' THEN 'NOSM'
                      WHEN uni.university_code = 'NOSM-LAUR' THEN 'NOSM'
                      WHEN uni.university_code = 'LAUR-ALGM' THEN 'ALGM'
                      WHEN uni.university_code = 'LAUR-HRST' THEN 'HRST'
                      WHEN uni.university_code = 'DOMC' THEN 'CARL'
                      ELSE uni.university_code
                  END)
                     AS university_code
                     
                FROM      edcs_core.EDU_ACT_TYPE eduacttyp
            

            
inner join   edcs_unistat.ustat_enrolment_data enrol
on          enrol.EDU_ACT_TYPE_ID = eduacttyp.EDU_ACT_TYPE_ID
                     
                     
                 INNER JOIN edcs_unistat.uni_institution_type uniinst
                     ON enrol.uni_institution_type_id = uniinst.uni_institution_type_id
                                
                 INNER JOIN edcs_core.university uni 
                    ON uniinst.university_id = uni.university_id
                    
                 INNER JOIN edcs_unistat.reg_status_type regstatus
                     ON enrol.reg_status_type_id = regstatus.reg_status_type_id
                     
 
                 INNER JOIN edcs_unistat.fee_cat_type feetyp 
                 ON enrol.fee_cat_type_id = feetyp.fee_cat_type_id
                 
     
           WHERE enrol.rep_start_year IN ('2012', '2013')
             AND enrol.period IN ('FAL')
            AND regstatus.reg_status_type_code IN ('12','13','14','15','16','18')
             AND feetyp.fee_cat_type_code IN ('3', '4')
             )
         


GROUP BY rep_start_year,
         university_code,
         EDU_ACT_TYPE_CODE

ORDER BY rep_start_year, university_code, EDU_ACT_TYPE_CODE

Open in new window

0
mariita
Asked:
mariita
  • 2
1 Solution
 
sdstuberCommented:
I think you're missing a comma between lines 17 and 21


 eduacttyp.edu_act_type_en_desc  ,   uni.university_id

Open in new window


or, since you don't really use uni.university_id in the results, just remove it entirely
0
 
slightwv (䄆 Netminder) Commented:
Another possibility:
If you are using sqlplus, by default it hates blank lines and they can cause this error.

You can allow blank lines in sqlplus with:
set sqlblanklines on

http://docs.oracle.com/cd/E11882_01/server.112/e16604/ch_twelve040.htm#i2678904
0
 
mariitaAuthor Commented:
I added the missing comma and now I'm getting a "column ambiguously defined" error. It seems to be referring to the GROUP BY clause.
0
 
sdstuberCommented:
you have 2 university_code columns in your inner query.
my guess is you don't want uni.university_code but instead want your case statement that creates a column of the same name


  SELECT rep_start_year,
         university_code,
         edu_act_type_code,
         COUNT(*) AS edu_act_type_count,
         edu_act_type_en_desc
    FROM (SELECT enrol.rep_start_year,
                 CASE
                     WHEN edu_act_type_code IN ('11',
                                                '19',
                                                '20',
                                                '21',
                                                '30',
                                                '31',
                                                '40',
                                                '41')
                     THEN
                         'Other'
                     ELSE
                         edu_act_type_code
                 END
                     edu_act_type_code,
                 eduacttyp.edu_act_type_en_desc,
                 uni.university_id,
                 (CASE
                      WHEN uni.university_code = 'DOMC' THEN 'CARL'
                      WHEN uni.university_code = 'NOSM-LAKE' THEN 'NOSM'
                      WHEN uni.university_code = 'NOSM-LAUR' THEN 'NOSM'
                      WHEN uni.university_code = 'LAUR-ALGM' THEN 'ALGM'
                      WHEN uni.university_code = 'LAUR-HRST' THEN 'HRST'
                      WHEN uni.university_code = 'DOMC' THEN 'CARL'
                      ELSE uni.university_code
                  END)
                     AS university_code
            FROM edcs_core.edu_act_type eduacttyp
                 INNER JOIN edcs_unistat.ustat_enrolment_data enrol
                     ON enrol.edu_act_type_id = eduacttyp.edu_act_type_id
                 INNER JOIN edcs_unistat.uni_institution_type uniinst
                     ON enrol.uni_institution_type_id = uniinst.uni_institution_type_id
                 INNER JOIN edcs_core.university uni ON uniinst.university_id = uni.university_id
                 INNER JOIN edcs_unistat.reg_status_type regstatus
                     ON enrol.reg_status_type_id = regstatus.reg_status_type_id
                 INNER JOIN edcs_unistat.fee_cat_type feetyp
                     ON enrol.fee_cat_type_id = feetyp.fee_cat_type_id
           WHERE enrol.rep_start_year IN ('2012', '2013')
             AND enrol.period IN ('FAL')
             AND regstatus.reg_status_type_code IN ('12',
                                                    '13',
                                                    '14',
                                                    '15',
                                                    '16',
                                                    '18')
             AND feetyp.fee_cat_type_code IN ('3', '4'))
GROUP BY rep_start_year, university_code, edu_act_type_code
ORDER BY rep_start_year, university_code, edu_act_type_code


also, unless you're going to use the university_id, it makes more sense to remove it

  SELECT rep_start_year,
         university_code,
         edu_act_type_code,
         COUNT(*) AS edu_act_type_count,
         edu_act_type_en_desc
    FROM (SELECT enrol.rep_start_year,
                 CASE
                     WHEN edu_act_type_code IN ('11',
                                                '19',
                                                '20',
                                                '21',
                                                '30',
                                                '31',
                                                '40',
                                                '41')
                     THEN
                         'Other'
                     ELSE
                         edu_act_type_code
                 END
                     edu_act_type_code,
                 eduacttyp.edu_act_type_en_desc,
                 (CASE
                      WHEN uni.university_code = 'DOMC' THEN 'CARL'
                      WHEN uni.university_code = 'NOSM-LAKE' THEN 'NOSM'
                      WHEN uni.university_code = 'NOSM-LAUR' THEN 'NOSM'
                      WHEN uni.university_code = 'LAUR-ALGM' THEN 'ALGM'
                      WHEN uni.university_code = 'LAUR-HRST' THEN 'HRST'
                      WHEN uni.university_code = 'DOMC' THEN 'CARL'
                      ELSE uni.university_code
                  END)
                     AS university_code
            FROM edcs_core.edu_act_type eduacttyp
                 INNER JOIN edcs_unistat.ustat_enrolment_data enrol
                     ON enrol.edu_act_type_id = eduacttyp.edu_act_type_id
                 INNER JOIN edcs_unistat.uni_institution_type uniinst
                     ON enrol.uni_institution_type_id = uniinst.uni_institution_type_id
                 INNER JOIN edcs_core.university uni ON uniinst.university_id = uni.university_id
                 INNER JOIN edcs_unistat.reg_status_type regstatus
                     ON enrol.reg_status_type_id = regstatus.reg_status_type_id
                 INNER JOIN edcs_unistat.fee_cat_type feetyp
                     ON enrol.fee_cat_type_id = feetyp.fee_cat_type_id
           WHERE enrol.rep_start_year IN ('2012', '2013')
             AND enrol.period IN ('FAL')
             AND regstatus.reg_status_type_code IN ('12',
                                                    '13',
                                                    '14',
                                                    '15',
                                                    '16',
                                                    '18')
             AND feetyp.fee_cat_type_code IN ('3', '4'))
GROUP BY rep_start_year, university_code, edu_act_type_code
ORDER BY rep_start_year, university_code, edu_act_type_code
0

Featured Post

[Webinar On Demand] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

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