CODEWITHSUNDEEP
oracle-databaseplsql
Gift Kamogelo
Gift Kamogelo

Reputation: 15

pl/sql SQL Statement ignored and missing right parenthesis

this code has to sum salary of employees of department_id 100.so it gives this error "missing right parenthesis"

DECLARE
v_department_name VARCHAR(100);
v_department_manager VARCHAR(100);
v_totalsalary NUMBER(30);

BEGIN

SELECT departments.department_name, concat(employees.first_name, 
employees.last_name), employees.salary INTO v_department_name, 
v_department_manager, v_totalsalary 
FROM employees JOIN departments ON employees.department_id = 
departments.department_id 
WHERE employees.salary = (SELECT departments.department_id, 
sum(employees.salary)
FROM EMPLOYEES
where departments.department_id=100
GROUP BY DEPARTMENT_ID
ORDER BY DEPARTMENT_ID);

DBMS_OUTPUT.PUT_LINE ('Department Name is  : ' || v_department_name || 'And 
Department Manager is : ' || v_department_manager || 'Total Amount of Salary 
is : ' || v_totalsalary );

END;

Upvotes: 0

Views: 396

Answers (1)

user5683823
user5683823

Reputation:

The "missing right parenthesis" error is clearly caused by the ORDER BY clause in the subquery (where it is not allowed).

Once you clear that error, you get the "too many values" error, because you are comparing a single variable (salary) to the output from a subquery that returns two values (department_id AND sum(salary)). Not sure why you thought you need to include the department_id in the SELECT clause of the subquery.

When you include error messages in your question, include the full text of the message (which shows the line number and position at which the error occurred - a crucial detail!)

Take it one small step at a time. Forget for the moment PL/SQL; are you able to write the correct query in SQL, which will return the department name, the manager's name and the sum of the salaries of all the employees in the department? If you can do that, then the PL/SQL around it is easy.

Here is one way to get all the values in one SQL statement:

select   d.department_name,
         m.first_name || ' ' || m.last_name as manager_name,
         sum(e.salary) as sum_salary
from     departments d
         join
         employees   m   on d.manager_id    = m.employee_id
         join
         employees   e   on d.department_id = e.department_id
where    d.department_id = 100
group by d.department_id, d.department_name, m.first_name, m.last_name
;

DEPARTMENT_NAME  MANAGER_NAME     SUM_SALARY
---------------  ---------------  ----------
Finance          Nancy Greenberg       51608

Perhaps 80% of writing good PL/SQL code is simply writing good, efficient SQL statements. If you have any difficulty with this query, you should probably spend the majority of your time writing SQL statements, for the next few days or weeks; return to PL/SQL when you feel this query (in my answer) is "simple", "easy", "standard" (which it is!)

Upvotes: 1

Related Questions