1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

oracle sql small problem

Discussion in 'HTML, Graphics & Programming' started by suki, 2 May 2006.

    2 May 2006 at 13:36
      #1
  1. suki

    Hitman

    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;
     
    Trust
    2. 3 May 2006 at 09:37
      #2
  2. Andre

    Mobster

    Joined: 25 Feb 2003

    Posts: 2,651

    Location: Deep dark hole

    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
     
    Trust