Solved

FROM keyword not found where expected

Posted on 2014-09-24
4
144 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

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Help With Database JOIN 7 28
SQL Help 27 46
string fuctions 4 26
Where Does Time Value Come From for Database Insert or Update 4 28
Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…

822 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