471,854 Members | 1,577 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,854 software developers and data experts.

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 1232

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 YellowAndGreen | last post: by
aboka
reply views Thread by aboka | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.