Solved

FROM keyword not found where expected

Posted on 2014-09-24
4
139 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
  • 2
4 Comments
 
LVL 73

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 76

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 73

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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
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.

744 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

12 Experts available now in Live!

Get 1:1 Help Now