Solved

FROM keyword not found where expected

Posted on 2014-09-24
4
146 Views
Last Modified: 2014-09-24
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
Comment
Question by:mariita
[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
  • 2
4 Comments
 
LVL 74

Expert Comment

by:sdstuber
ID: 40341537
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
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40341575
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
 

Author Comment

by:mariita
ID: 40341737
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
 
LVL 74

Accepted Solution

by:
sdstuber earned 500 total points
ID: 40341781
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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows how to recover a database from a user managed backup

726 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