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.

  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;
    
     
  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