Results 1 to 5 of 5

Thread: sub query

  1. #1
    Join Date
    Apr 2004
    Posts
    38

    Unanswered: sub query

    Code:
    select address from customer where 
    ownerid = (select ownerid from owner where 
    fishermanid = (select fishermanid from catch where date =('4-oct-04') ))
    and fishid =(select fishid from fish where fishtype='shark')
    	and
    	carid=(select carid from car where model='falcon')
    I got an error message from this code, line 2 missing parameter
    Could you please help me
    Thanks,
    Frenk
    Last edited by fj8283888; 05-26-04 at 09:23.

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Do you receive the same error message when you delete two empty lines from your query? Second and fourth, that is?

  3. #3
    Join Date
    Apr 2004
    Posts
    38

    error

    in the original code, it did not contain the empty line
    so I don't think it's the reason

    I guess it's the place I put the bracket, I may put it wrongly. If you could help me, it would be great


    I will adjust it again

    Thanks,
    Frenk

  4. #4
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    TOAD's formatter didn't find anything strange in your code ...
    PHP Code:
    SELECT address
      FROM customer
     WHERE ownerid 
    = (SELECT ownerid
                        FROM owner
                       WHERE fishermanid 
    = (SELECT fishermanid
                                              FROM 
    catch
                                             
    WHERE DATE = ('4-oct-04')))
       AND 
    fishid = (SELECT fishid
                       FROM fish
                      WHERE fishtype 
    'shark')
       AND 
    carid = (SELECT carid
                      FROM car
                     WHERE model 
    'falcon'

  5. #5
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    That layout is a little hard to follow. I reformatted like this to see what goes with what:

    Code:
    select address from customer
    where ownerid = (select ownerid from owner
                     where fishermanid = (select fishermanid from catch
                                          where date =('4-oct-04') 
                                         )
                    )
    and fishid = (select fishid from fish where fishtype='shark')
    and carid = (select carid from car where model='falcon')
    There is an issue with having a column called DATE, because DATE is a reserved word in Oracle. When I try to run the above I get:

    Code:
                                          where date =('4-oct-04')
                                                *
    ERROR at line 4:
    ORA-00936: missing expression
    If I change DATE to CATCH_DATE then I get:
    Code:
    and carid = (select carid from car where model='falcon')
        *
    ERROR at line 8:
    ORA-00904: invalid column name
    ... which makes sense as I haven't created the tables!

    However, I don't expect this query to work as is, because it expects all the subqueries to return exactly one row (is there only one fish of type 'shark', only one fisherman who made a catch on that date, etc.?) Probabably you need to change the = to IN.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •