CODEWITHSUNDEEP
sqloracle-databaseplsql
Sam
Sam

Reputation: 193

SQL command not properly ended PL/SQL

From what I understand, this error is caused by clauses that are not allowed by the statements, but I don't see anything wrong with this query.

accept cruiseRange prompt "Enter cruising range: ";

CREATE OR REPLACE PROCEDURE 
   runSqlAndGetManyTuples (cruiseRange NUMBER)
  IS planeName varchar2(20); planeID NUMBER; averageSalary NUMBER;

  CURSOR myTuplesAreHereCursor IS
   SELECT Temp.pName, Temp.pID, Temp.avgSalary INTO planeName, planeID, averageSalary
   FROM (SELECT A.airplaneID AS pID, A.airplaneName AS pName, AVG(E.salary) AS avgSalary
          FROM cskresman.Airplane A, cskresman.Certified C, cskresman.Employees E
          WHERE A.airplaneID = C.airplaneID AND C.employeeID = E.employeeID AND A.cruisingRange > cruiseRange
          GROUP BY A.airplaneID, A.airplaneName) AS Temp;

Upvotes: 0

Views: 101

Answers (2)

Popeye
Popeye

Reputation: 35930

You can not use the INTO in cursor query and also you can not use the AS for table or sub-query alias.

Your cursor query should look like this:

CURSOR myTuplesAreHereCursor IS
   SELECT A.airplaneID AS pID, 
          A.airplaneName AS pName, 
          AVG(E.salary) AS avgSalary
    FROM cskresman.Airplane A, 
         cskresman.Certified C, 
         cskresman.Employees E
   WHERE A.airplaneID = C.airplaneID
     AND C.employeeID = E.employeeID 
     AND A.cruisingRange > cruiseRange
GROUP BY A.airplaneID, A.airplaneName;

Suggestion: Always use standard ANSI joins and sub-query is not required at all.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1271161

  1. Never use commas int he FROM clause. Always use proper, explicit, standard, readable JOIN syntax.

  2. The subquery is not necessary.

  3. Oracle does not support as for table aliases.

You can write the query as:

SELECT A.airplaneID AS pID, A.airplaneName AS pName, AVG(E.salary) AS avgSalary
INTO planeName, planeID, averageSalary
FROM Airplane A JOIN
     Certified C
     ON A.airplaneID = C.airplaneID JOIN
     Employees E
     ON C.employeeID = E.employeeID 
WHERE A.cruisingRange > cruiseRange
GROUP BY A.airplaneID, A.airplaneName;

Upvotes: 1

Related Questions