outer join in stored procedure

[leigh]

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!

[chrisrlong]

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

[leigh]

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;

[chrisrlong]

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

[leigh]

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.

[kmesser]

How about:

. . . where . . .

AND rownum = 1;

[leigh]

I should have though of this earlier...
Anyway, thank you!