-
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!
-
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
-
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;
-
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
-
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.
-
How about:
. . . where . . .
AND rownum = 1;
-
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|