SAP (Documents/Screen shots/Complete Solution): RE:[oracle-db-l] ORA-00913 Too Many Values Error

Reply from SteveFLHSMV on Mar 22 at 2:35 AM

You are trying to use 1 case statement to determine the values for several different items. I have often wished there was such a construct, but case requires the then and else to be a single value. This case can be handled with union all. The inner join in the 1st select handles all of the exists cases implicitly and the the 2nd select uses not exists to handle the else cases explicitly.

I prefer to separate what would be ambiguous output columns, but this is enough to tackle without the inclusion of a blank phone number column in the 1st select.

select
'Employee' "Classification".
e.employee_id "Person ID",
e.first_name "Name or Phone",
d.department_name "Department ID",
l.location_id "Location ID"
from hr.employees e,
hr.departments d,
hr.locations l
where e.department_id=d.department_id
and d.location_id=l.location_id
union all
select
'Manager',
e.manager_id,
e.phone_number,
d.department_name,
l.location_id
from hr.employees e,
hr.departments d,
hr.locations l
where e.employee_id=d.department_id
and d.location_id=e.location_id
and not exists
(
select 1
from hr.employees e,
hr.departments d
where e.department_id=d.department_id
) ;

---------------Original Message---------------
From: sravani guntuka
Sent: Thursday, March 22, 2012 1:23 AM
Subject: ORA-00913 Too Many Values Error

I'm getting error'.(ORA-00913 too many values )' while running below query in database

select the records based on condition

Eg:

select
(

case
when
exists
(
select 1
from hr.employees e,
hr.departments d
where e.department_id=d.department_id
)
then
(
select
e.employee_id,e.first_name,
d.department_name,l.location_id
from hr.employees e,
hr.departments d,
hr.locations l
where e.department_id=d.department_id
and
d.location_id=l.location_id
)
else
(
select
e.manager_id,e.phone_number,
d.department_name,l.location_id
from hr.employees e,
hr.departments d,
hr.locations l
where e.employee_id=d.department_id
and d.location_id=e.location_id
)
end
)

from hr.employees e,
hr.departments d,
hr.locations l

Please resolve the problem.

Reply to this email to post your response.

__.____._

Manage Settings | Unsubscribe | Create FAQ | Send Feedback