RE: Simple Display file problem

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