Outer Join Problem (I think!) 1

[MikeJones]

Hi All,

I've got a cursor that looks a bit like this...

cursor my_cursor (in_other_value varchar2);
select *
from a, b
where a.foriegn_key = b.primary_key (+)
and nvl(b.other_value(+),'^^' = nvl(nvl(in_other_value,b.other_value),'^^)

I thought this would work but it doesn't, My problem is that I must outer join to b as many of the records from a do not have a corresponding record on b, if no value is passed in in in_other_value then it should not restirct on it. So I thought compare the value from the outer join to the value passed in, but if they are both null then it will get rejected to nvl them again to an arbitry string (^^) so that they will match. This isn't working though. If I supply a value in in_other_value then all rows get returned, not just those that match the value passed in.

HELP!!

Mike.

 

[listener22]

Hi Mike
Hope this will help.
Ignoring the nvl's, the cursor is to return all records
from a and b, even where a.foreign_key doesn't find
a match in b.primary_key, and even when the parameter
passed in doesn't find a match in b.other_value, except
that in the cases of no matches, the record from b will
be null but the record from a will be populated.
Is that what you wnat, or do you want the records
from b, whether or not there are matches to a?
Jim

oracle, vb

 

[MikeJones]

Thanks for the reply, to be honest I've come up with a workaround, the in value is no longer b.other value but b.primary key, this makes it alot eaiser. However, if there is a solution I would love to here it as it's one I am likely to face time and time again.

"the cursor is to return all records
from a and b, even where a.foreign_key doesn't find
a match in b.primary_key" Yes all records from a shouldbe returned.

"and even when the parameter
passed in doesn't find a match in b.other_value" If the parameter is specified then only those rows which have a match in b.other_value should be returned, this is where I believe the cursors logic falls apart.

"except that in the cases of no matches, the record from b will be null but the record from a will be populated." I'm not sure what you mean here, if there is no record in B for a row from A and the parameter has been specified then I would expect no row to be returned, however if the parameter has not been specified then I would expect the row from A to be returned and all the B rows to be null.

Thanks again,

Mike.

 

[listener22]

Hi Mike -

In your paragraph above,

"except that in the cases of no matches, the record from b will be null
but the record from a will be populated." I'm not sure what you mean*
here, if there is no record in B for a row from A and the parameter has
been specified then I would expect no row to be returned, however if the
parameter has not been specified then I would expect the row from A to
be returned and all the B rows to be null.


* I meant that because the +'s are on the a database side, so to speak, that
nulls will be returned for the b-columns in those cases of no matches.


For your paragraph,

"and even when the parameter
passed in doesn't find a match in b.other_value" If the parameter is
specified then only those rows which have a match in b.other_value
should be returned, this is where I believe the cursors logic falls apart.

the inclusion of the (+) for the in parameter matching is what defeated the
results you wanted. Jim

oracle, vb

 

[MikeJones]

Nice one, thanks again.

 

[CRoberts]

There is a number of ways to proceed, depending on what you really need and the stucture of your data. The outer join should be on your foreign key as it may or may not be NULL. Your approach is rather more succinct than other approaches, but it is not too good if you have large tables, a DECODE or NVL will force full table scans. The trick is here is not to use a NVL but a DECODE.

CURSOR my_cursor (in_other_value varchar2) IS
select *
from a, b
where a.foreign_key(+) = b.primary_key
and DECODE(in_other_value, NULL, 'x', b.other_value) =
DECODE(in_other_value, NULL, in_other_value);

Another approach would be to have a test on in_other_value or have an exception to deal with no rows returned. Oracle 8i allows weakly typed cursors to be defind on the fly, which can the same thing sort of thing. Your could, for efficiency sake, divide you calls into two nested cursor.

 

[MikeJones]

Hi CRoberts,

Unfortunatly the actual cursor I'm dealing with isn't as simple as the example I posted, the main driving table can have over 3 million rows as some new customers have proved, it has to do this sort of outer join four times. but the tables that are outer joined to a re very small under 1000 rows each.

For the reocrd, I can't have 2 cursors as I am extending the functionality for a new environment, but any code I produce must be fully backwards compatable for old version of the front end (which is why if the parameter is not provided no restriction should happen as the old front end has no visibilty of these new parameters).

However, I'm very concerned about full table scans (Due to the size of some of the tables) currently it has none as there is an index on every column I join with, Will I get full scans if I use DECODEs and NVLs or just NVL's??

Also, I don't suppouse you could elaborate on "nested cursors" and weakly typed defined on the fly could you??

Thanks in advance,

Mike.