469,643 Members | 2,064 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,643 developers. It's quick & easy.

Outer join problem

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)
Jul 16 '07 #1
0 1170

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

2 posts views Thread by Martin | last post: by
8 posts views Thread by Matt | last post: by
3 posts views Thread by Martin | last post: by
4 posts views Thread by Anthony Robinson | last post: by
reply views Thread by gheharukoh7 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.