?
Solved

FROM keyword not found where expected

Posted on 2014-09-24
4
Medium Priority
?
149 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 2000 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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to take different types of Oracle backups using RMAN.
Suggested Courses

801 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