1
I'm running a query involving an outer join where one of the conditions is that the numeric value of a varchar field is between the numeric values of 2 varchar fields in another table (this has to do with an address range where in some cases the addresses include an alpha character). The query runs fine in the original database but for some reason it returns an "invalid number" error in another db against the exact same data, even though I am eliminating non-numeric values.
Something like:
select *
from table1 a
left outer join table2 b
on
<several join conditions here>
and
translate(a.primary_nbr, '*0123456789 ', '*') is null --eliminate non-numeric primary number
and translate(b.low_nbr, '*0123456789 ', '*') is null --eliminate non-numeric ranges
and to_number(a.primary_nbr) between to_number(b.low_nbr) and to_number(b.high_nbr)
where...
I would assume Oracle evaluates the join predicates in the order they appear (as it does in WHERE clauses). I've done this same thing without any problems many times but this particular db doesn't like it.
Apparently the ORDERED_PREDICATES hint applies only to the WHERE clause and not to join conditions - is there another hint or any other possible solution for this?
(Interestingly - if the outer join is changed to an inner join the query executes properly)
