Results 1 to 5 of 5

Thread: Multiple rows

  1. #1
    Join Date
    Jan 2004
    Location
    UK
    Posts
    21

    Unanswered: Multiple rows

    Hi I have this function

    CREATE TYPE BODY licence_t AS MEMBER FUNCTION get_penalty_points RETURN INTEGER IS
    p number;
    y number;
    BEGIN
    select z.penalty_p into p from conv_speed z, licence a where z.speed_li = ref(a) ;
    return y = y + p;
    END get_penalty_points;
    END;
    /

    My goal is to get all the values of a tuple that belong to a person ID into P.
    but it will return more than one row.
    I get this error

    select z.get_penalty_points() from licence z
    *
    ERROR at line 1:
    ORA-01422: exact fetch returns more than requested number of rows
    ORA-06512: at "OPS$MYID.LICENCE_T", line 5
    ORA-06512: at line 1


    thanks

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >but it will return more than one row.
    >I get this error
    That is correct.
    So how do you plan/expect a function to return multiple values?
    It appears you have a serious design problem.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Apr 2004
    Location
    USA
    Posts
    33
    Why don't you do sum(z.penalty_p)?

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >Why don't you do sum(z.penalty_p)?
    When OP clearly stated
    "My goal is to get all the values of a tuple that belong to a person ID into P",
    I fail to see how any single number satisfies the initial requirement.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  5. #5
    Join Date
    Jan 2004
    Location
    UK
    Posts
    21
    Sorry I stated wrong.
    I want to add all the values from a ID. the thing is.
    A licence driver may have many penalty points. now I want to query a list of
    all the penalty points a licence had. so if a licence had 3 penalty each with 2points
    it would give 6 for that id.
    I will try the sum thing

Posting Permissions

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