× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



<snip>
If you get a 'no-hit' on your join file, why not do a chain or SQL to
the
individual physical that has the data you want.
</snip>

The solution is much simpler. Just use a Left Outer Join. The primary
record will always be read no matter is there is a record in the
secondary or not. You can then test for null or blanks in the outer
table. 

<snip>
Yeah, that's very cool.  It's a set-based function and it's the kind of
thing SQL is made for.  It's also just a tiny bit scary; you have an
unqualified join which means you're creating a Cartesian product.  I'm
not
sure if the "WHERE" clause is applied BEFORE or AFTER the JOIN is
applied.
Theoretically you're calculating up to 10 billion distances every time
you
do this.  But realistically the number is probably far smaller, and I
guess
if you think about it, how else are you going to do it?
</snip>

The solution here is Ops Nav. Put the SQL into a Run SQL script and then
request a Visual Explain and it will show you exactly how it plans to
run the query. 

How SQL decides to run the query has nothing to do with how you put it
in. That's why I always run in Ops Nav on any complicated query to make
sure it is not doing something funny.    

Make sure that you use V5R3 and V5R4 version of Client Access. They have
much, much improved SQL functions. I believe both run on V5R2 OS/400 up.

Also, on the question of position to SQL result couldn't you do a
subquery to return the mileage result and then select the result based
on a mileage range that is input? 

SQL does support what you want, just not the way you thinking about it.



As an Amazon Associate we earn from qualifying purchases.

This thread ...

Follow-Ups:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.