oracle sql small problem

Associate
Joined
31 Oct 2003
Posts
526
hi

i get the following error message when running the query below.
can anyone please tell me whats wrong with the query

thanks very much in advance


Date from: 12-jan-2006
Date to : 28-dec-2006
(select ACTOR_ID, count(*) INTERVIEWS
*
ERROR at line 2:
ORA-00913: too many values

Commit complete.
SQL>

my query now

Code:
rem most interviewed actor between date1 and date2
host clear
set verify off
prompt
prompt MOST intervied actor between dates;
accept d1 prompt 'Date from: ';
accept d2 prompt 'Date to : ';
select * from actor where actor_id in 
(select ACTOR_ID, count(*) INTERVIEWS
from INTERVIEW where I_DATE between '&d1' and '&d2'
group by actor_id having count(*) >= all
(select count(*) from INTERVIEW
where I_DATE between '&d1' and '&d2' group by ACTOR_ID)); 
commit;
 
Soldato
Joined
25 Feb 2003
Posts
2,708
Location
Deep dark hole
suki said:
hi

i get the following error message when running the query below.
can anyone please tell me whats wrong with the query

thanks very much in advance


Date from: 12-jan-2006
Date to : 28-dec-2006
(select ACTOR_ID, count(*) INTERVIEWS
*
ERROR at line 2:
ORA-00913: too many values

Commit complete.
SQL>

my query now

Code:
rem most interviewed actor between date1 and date2
host clear
set verify off
prompt
prompt MOST intervied actor between dates;
accept d1 prompt 'Date from: ';
accept d2 prompt 'Date to : ';
select * from actor where actor_id in 
(select [B]ACTOR_ID, count(*) INTERVIEWS[/B]
from INTERVIEW where I_DATE between '&d1' and '&d2'
group by actor_id having count(*) >= all
(select count(*) from INTERVIEW
where I_DATE between '&d1' and '&d2' group by ACTOR_ID)); 
commit;

The bit in bold is the problem - you cannot have more than one field returned from the sub-select. Get rid of the count(*) INTERVIEWS from within that select and it will return a single value that can be used in the outer select
 
Back
Top Bottom