Sql error in Oracle 10g XE

error message sql command not properly ended

Page 1 of 1

4 Replies - 1153 Views - Last Post: 11 April 2009 - 06:33 PM

#1 wartech  Icon User is offline

  • D.I.C Head
  • member icon

Reputation: 10
  • View blog
  • Posts: 203
  • Joined: 16-October 06

Sql error in Oracle 10g XE

Posted 11 April 2009 - 03:43 PM

Hello,
I am getting the error message "sql command not properly ended" when I try to run the following query. I am using Oracle 10g XE. I do not understand what I am missing in this statement. Your help is appreciated.

select e.last_name,e.department_id,j.job_id,j.job_title,MIN(e.salary),MAX(e.salary)
from employees e, jobs j
on e.job_id = j.job_id
group by e.department_id
having e.department_id in (50,60)
order by e.job_id,e.last_name;




Is This A Good Question/Topic? 0
  • +

Replies To: Sql error in Oracle 10g XE

#2 June7  Icon User is offline

  • D.I.C Addict
  • member icon

Reputation: 104
  • View blog
  • Posts: 904
  • Joined: 09-December 08

Re: Sql error in Oracle 10g XE

Posted 11 April 2009 - 04:24 PM

I don't know oracle but do use SQL and your string looked good to me so searched and found example from this site, it uses JOIN keyword, maybe makes a difference:
http://www.sqlinfo.n...acle_SELECT.php
SELECT  
	   b.COURSE_NAME		,
	   a.CLASS_END_DATE	 , 
	   a.CLASS_START_DATE						 
 
FROM		 CLASSES a 
		JOIN COURSES b 
			 ON  b.COURSE_DESIGNATER = a.COURSE_DESIGNATER_FK

Was This Post Helpful? 0
  • +
  • -

#3 wartech  Icon User is offline

  • D.I.C Head
  • member icon

Reputation: 10
  • View blog
  • Posts: 203
  • Joined: 16-October 06

Re: Sql error in Oracle 10g XE

Posted 11 April 2009 - 05:00 PM

Hi June7,
When I used the join I received the error ORA-00979: not a GROUP BY expression

The only way I can get this code to work somewhat is if I use this code:

select e.last_name,e.department_id,j.job_id,j.job_title
from employees e join jobs j
on e.job_id = j.job_id
where e.department_id in (50,60)
order by e.job_id,e.last_name;





My problem seems to be when I am using MAX and MIN
Was This Post Helpful? 0
  • +
  • -

#4 baavgai  Icon User is offline

  • Dreaming Coder
  • member icon

Reputation: 5761
  • View blog
  • Posts: 12,574
  • Joined: 16-October 07

Re: Sql error in Oracle 10g XE

Posted 11 April 2009 - 06:04 PM

Let's look at one table first:
select e.job_id, e.last_name, e.department_id, MIN(e.salary), MAX(e.salary)
	from employees e
	where e.department_id in (50,60)
	group by e.job_id, e.last_name, e.department_id



That should give you the first bit of data you say you're lookng for. Now for the jobs table contribution.

select e.last_name,
		e.department_id,
		j.job_id,
		j.job_title,
		e.salary_min, e.salary_max
	from (
		select e.job_id, e.last_name, e.department_id, 
				MIN(e.salary) as salary_min, MAX(e.salary) as salary_max
			from employees e
			where e.department_id in (50,60)
			group by e.job_id, e.last_name, e.department_id
	) e
		inner join jobs j
			on e.job_id = j.job_id
	order by e.job_id,e.last_name;



That should work. You can get away without the sub query, by using a larger group by. Howevr, the above version is probably more efficient.
Was This Post Helpful? 0
  • +
  • -

#5 wartech  Icon User is offline

  • D.I.C Head
  • member icon

Reputation: 10
  • View blog
  • Posts: 203
  • Joined: 16-October 06

Re: Sql error in Oracle 10g XE

Posted 11 April 2009 - 06:33 PM

Thanks Baagvi!! It works and I understand where I went wrong.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1