CODEWITHSUNDEEP
sqloracle-database
rain
rain

Reputation: 15

SQL statement error

Print the names of employees who are certified only on aircrafts with cruising range longer than 1000 KMs.

SELECT E.ename
FROM Employees E, Certified C, Aircraft A
WHERE C.aid = A.aid AND E.eid = C.eid
GROUP BY E.eid, E.ename
HAVING EVERY (A.cruisingrange > 1000)

Oracle's lack of support for the HAVING EVERY clause produces the error

ORA-00907: missing right parenthesis

How to fix it?

Upvotes: 1

Views: 83

Answers (1)

sagi
sagi

Reputation: 40481

Instead of every, select the smallest one, and check that its bigger than 1000

SELECT E.ename
FROM Employees E
INNER JOIN Certified C ON  E.eid = C.eid
INNER JOIN Aircraft A ON C.aid = A.aid
GROUP BY E.ename
HAVING MIN(A.cruisingrange) > 1000

As Gordon said, I forgot to mention, I fixed your join syntax from implicit(comma separated) to explicit join syntax. Try to avoid the use of implicit join syntax in general that can lead to mistakes when dealing with more then two tables.

Upvotes: 5

Related Questions