× 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.



From: Mark Adkins

SQL's set based nature allows for some very elegant solutions.  Display
programs are a perfect fit for SQL cursors.  I only wish IBM would
implement the ability to position on data values within a cursor.

IBM is unlikely to implement anytime soon this because SQL doesn't support
it.  Although this is an intrinsic capability of ISAM access, it never made
it onto the radar of the SQL developers.  There is no way to create a cursor
and then position by key within that cursor.  Some day they'll have a way,
I'm sure (in fact, I think it's already on the design table), but why do it
when you already have native I/O?

Try doing something like this using RLA.  The file contains zipcodes and
lat/long values.  STRQM will prompt the user for &zip and &mileage when it
is run.

SELECT B.ZIPCODE AS ORIGINALZIP, A.ZIPCODE AS DESTINATIONZIP,
       CAST(SQRT( POWER(ABS(A.LAT-B.LAT)*69.1,2) +
                  POWER(ABS(A.LONG-B.LONG)*53,2) ) AS DEC(5,2))
        AS MILEAGE
FROM BS660F AS A
 INNER JOIN
     BS660F AS B
  ON SQRT( POWER(ABS(A.LAT-B.LAT)*69.1,2) +
           POWER(ABS(A.LONG-B.LONG)*53,2) ) < &MILEAGE
WHERE B.ZIPCODE = &ZIP
ORDER BY MILEAGE DESC

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?

By the way, I think you could probably create something like this using an
OPNQRYF (that is, if it supports SQRT), and then you would be able to search
using your standard keyed access.  Then if someone decided to check a second
time with a different mileage, you wouldn't have to re-run the query.

And of course, in RLA I could CHAIN out and get the original latitude and
longitude of the first ZIP code and then just create a simple non-JOIN
OPNQRYF (again supposing that OPNQRYF supports SQRT).  Even in SQL it might
be quicker:

SELECT &OZIP AS ORIGINALZIP, ZIPCODE AS DESTINATIONZIP,
       CAST(SQRT( POWER(ABS(LAT-&OLAT)*69.1,2) +
                  POWER(ABS(A.LONG-&OLONG)*53,2) ) AS DEC(5,2))
        AS MILEAGE
(...)

Not sure, but it's a thought.

Joe



As an Amazon Associate we earn from qualifying purchases.

This thread ...

Replies:

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.