DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: outer join in stored procedure

  1. #1
    Join Date
    Apr 2001
    Posts
    13
    I have a stored procedure that needs to access many tables to collect data. One of the tables accessed doesn't necessary have a matching row (which means in plain sql statement, this requires an outer join), my question is how do I enable outer join here. I tried the same way by putting a (+) here, the "NO data found" error was returned.

    Thank You for the help!


  2. #2
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    Yes, (+) is what you need.

    Table A Table B
    1............1
    2............
    ..............3
    4............4

    SELECT * FROM TA, TB
    WHERE TA.C1 = TB.C1

    1..1
    4..4

    SELECT * FROM TA, TB
    WHERE TA.C1(+) = TB.C1

    1..1
    ....3
    4..4

    SELECT * FROM TA, TB
    WHERE TA.C1 = TB.C1(+)

    1..1
    2...
    4..4


    HTH,

    - Chris

  3. #3
    Join Date
    Apr 2001
    Posts
    13
    Thank You for the reply.
    Below is a brief version of code similar to what I wanted to accomplish. Table2 either doesn't contain a matching row or contains more than 1 matching row. How should I change the code to either assign v_num1 and v_num2 an 0 to non-matched id or select only the first matched row for id that has more than one matching row?


    CURSOR C_OP (I_OP_ID NUMBER) IS
    SELECT DISTINCT a.id
    FROM Table1 a
    WHERE a.id=v_op_id;
    C_OP_REC C_OP%ROWTYPE;


    OPEN C_OP(V_OP_ID)
    LOOP
    FETCH C_OP INTO C_OP_REC;
    EXIT WHEN C_OP%NOTFOUND;
    v_id:=c_op_rec.id;

    BEGIN
    SELECT DISTINCT
    b.num1,
    b.num2
    INTO v_num1,
    v_num2
    FROM table2 b
    WHERE v_id=b.id(+);
    END;
    END LOOP;

  4. #4
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    First of all, I will simply have to assume that your real code is more complex than this, since there is no reason to do something this simple with a cursor.

    Given that, you are applying the wrong tool. An outer-join is used when you are combining 2 tables in a single statement. You are not. You have a second statement. You want to set 2 variables to 0 if this second statement does not return any values. Therefore:

    BEGIN
    ---SELECT DISTINCT
    ------b.num1,
    ------b.num2
    ---INTO
    ------v_num1,
    ------v_num2
    ---FROM
    ------table2 b
    ---WHERE
    ------v_id=b.id;
    EXCEPTION
    ---WHEN NO_DATA_FOUND THEN
    ------v_num1 := 0;
    ------v_num2 := 0;
    ---WHEN OTHERS THEN
    ------RAISE;
    END;

    Make sense?

    - Chris

  5. #5
    Join Date
    Apr 2001
    Posts
    13
    Thanks Chris for your prompt reply!

    With your idea, the NO_DATA_FOUND error will be fixed. but what if the id returns more than 1 row of data? will this be fixed too?

    Here is what I got;
    ORA -01422:exact fetch returns more than requested number of rows

    Thanks.

  6. #6
    Join Date
    Aug 2000
    Posts
    462
    How about:

    . . . where . . .

    AND rownum = 1;


  7. #7
    Join Date
    Apr 2001
    Posts
    13
    I should have though of this earlier...
    Anyway, thank you!

Posting Permissions

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


Click Here to Expand Forum to Full Width