November 2010

Tech Info

Calculating Straight-Line Distances in SEQUEL ViewPoint

You can use a number of SEQUEL ViewPoint functions to determine the straight line distance between two points.

The start and end points can be numeric latitude and longitude coordinates, or 5-digit U.S. zip codes. (For zip code calculations, SEQUEL uses a file called SEQUEL/DISTANCE that contains U.S. Department of Commerce zip code data.)

Note: When SEQUEL calculates the distance between zip codes, it measures from midpoint to midpoint.

Calculating straight-line distances is simple with SEQUEL functions. Our customers in the shipping and trucking industries find them invaluable, and marketers use them to organize mailings and produce customer and prospect lists based on geography.

Let’s take a look at a few examples.

Example 1: Calculate straight-line distance between two locations in kilometers

DIST_KM(latitude1, longitude1, latitude2, longitude2)

In this example, you supply the starting and ending latitude/longitude coordinates as numeric values for the DIST_KM function, as shown below.

SELECT dist_km(42.051927,-88.048140,41.891978,-88.205021)
NAME(DISTANCE_KM)
FROM sqlexec

The result is a value of 21.9911 kilometers for the DISTANCE_KM field.

Example 2: Using fields to calculate straight-line distance between two locations in kilometers

In this example, you use database fields—LAT1, LAT2, and so forth—that you’ve defined.

SELECT dist_km(LAT1, LONG1, LAT2, LONG2)
NAME(DISTANCE_KM)
FROM sqlexec

 Example 3: Calculate the straight-line distance between two locations in miles

 DIST_MILES(latitude1, longitude1, latitude2, longitude2)

This example is similar to the first example—you supply the starting and ending latitude/longitude coordinates as numeric values.

SELECT distmiles(42.051927,-88.048140,41.891978,-88.205021)
NAME(DISTANCE_M)
FROM sqlexec

The result is a value of 13.6591 miles for the DISTANCE_M field.

Example 4: Calculate the straight-line distance between zip codes in kilometers

DIST_REF_KM(zipcode1, zipcode2)

The DIST_REF function uses 5-character zip code values for the starting and ending values with the SEQUEL/DISTANCE file.

SELECT dist_ref_km(“60185”,“60173”)
NAME(DISTANCE_KM)
FROM sqlexec

The result is a value of 21.9911 kilometers for the DISTANCE_KM field.

Example 5: Calculate the straight-line distance between zip codes in miles

DIST_REF_MILES(zipcode1, zipcode2)

This example also uses zip codes for the starting and ending values.

SELECT dist_ref_miles
(“60185”,“60173”)
NAME(DISTANCE_M)
FROM sqlexec 

The result is a value of 13.6591 miles for the DISTANCE_M field.

Using Subqueries For Complex Data Requests

Do your data requests sometimes become very involved because the records selected depend on values in another record? Do you ever need to compare records in a file to other records in the same file? Or, do you ever build work files and query those files?

These are complex requests, and subqueries can help with all of them. A subquery lets you include a SELECT statement in a comparison within a WHERE clause or HAVING clause, and can return one value, or a list of values, for comparison.

For example, we’ll use some data from the SEQUELEX/ORDHEAD file, which contains one record per order.
Figure 1 shows a subset of its data.

Figure 1: A subset of our original list.

Subqueries That Return One Value

You can use a subquery to return a single grouped result to compare against data values in a primary record. For instance, here’s a subquery that finds all of the orders (ORDNO) whose retail value (ORVAL) is greater than the average for the entire order header (ORDHEAD).

SELECT cusno, ordno, orval
FROM sequelex/ordhead
WHERE orval > (SELECT AVG(orval)
FROM sequelex/ordhead
ORDER BY cusno Asc,ordno Asc

The average retail value of $9,753.72 is calculated once for the records in the file. Then, SEQUEL compares each retail value against this value and returns a row only if its retail value is greater than $9,753.72.

Subqueries That Return a List of Values

Sometimes a query makes comparisons against a list of values. For example, the subquery IN comparison is a simple way to test a field or expression result against a list of values.

Suppose that you want to see all of the orders for customers that have at least one backorder (an order type [OTYPE] of B). You can’t simply say: WHERE OTYPE=“B” because this query returns only type-B (backorder) order records. Using our sample data file, Figure 2 shows that the following records are returned:

Figure 2: This query returns only type-B order records.

But, we actually want to see all of the order records for customers 100112 and 100200, including their type-R and type-O records.

To see all the order records for these customers, we use the following query/subquery combination. First, the IN SELECT subquery selects all the type-B order records (it locates all customers with a type-B order record). Then, the outer SELECT query looks for all orders for this group of customers.

SELECT cusno, ordno, orval
FROM sequelex/ordhead
WHERE cusno IN(SELECT cusno
FROM sequelex/ordhead
WHERE otype=”B”)

How Do I Build A Subquery?

Now that you’ve seen some of the basic concepts of subqueries, you should try building one. SEQUEL ViewPoint prompts you through the process of building a subquery (the WHERE clause Expression Editor lists the subquery comparisons under the WHERE/HAVING section of the Function List).

By Steven Smith, Technical Consultant

What Do You Want To Do With SEQUEL?

Is there a specific SEQUEL “How-To” that you’d like to see in an upcoming issue? Have you developed your own SEQUEL solution that you’d like to share with other SEQUEL users?

We’d like to hear from you! Send your ideas to the SEQUEL Connections editors at connections@helpsystems.com.

Learn more with SEQUEL Webinars and online training.

Request a free trial.