Do you work with complex data requests in SEQUEL? You do if any of these situations sound familiar:

* The records you want to select are dependent on values in another record

* You need to compare records in a file to other records in the same file

* You build work files and query them to accomplish the data request

What you might not realize is that you can use subqueries help with these complex requests. Using a subquery, you can nest a SELECT statement to make additional comparisons in the WHERE or HAVING clauses. A subquery can return one value, or a list of values, for comparison.

Let’s look at an example using some data from the SEQUELEX/ORDHEAD file. This file contains one record per order. Figure 1 (below) shows a small subset of the data.

Subqueries that return one value
You can use a subquery to return a single grouped (summarized) result that you can compare to data values in a primary record.

For instance, you can find all the orders (ORDNO) whose Retail Value (ORVAL) is greater than the average for the entire Order Header (ORDHEAD) file as easily as this:

SELECT cusno, ordno, orval

FROM sequelex/ordhead

WHERE orval > (SELECT AVG (orval) FROM sequelex/ordhead)

ORDER BY cusno Asc, ordno Asc

After SEQUEL calculates the average retail value for the records in the file, it compares each retail value against the average and returns a row only if its retail value is higher than the average.

Subqueries that return a list of values
Sometimes you want your query to compare a value against a list of values. You may have used the IN comparison to test a field or an expression result against a list of values.

But, what if the situation is a bit more complicated? Suppose you have a list of customers and you want to see which ones have backorders. Not only do you want to see the backorder (an OTYPE of B), but also if the same customer made other purchases that are not back-ordered. The following statement won’t be enough.

WHERE OTYPE=”B”

This statement returns only orders that have a backorder, as shown in Figure 2.

We actually want to see all the order records, including the order types of R and O, for customers with backorders.

You can use the following query to select all orders for customers who also have a B order-type record.

SELECT cusno, ordno, orval

FROM sequelex/ordhead

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

It’s easy to build subqueries
ViewPoint makes it easy to build a subquery. The WHERE clause Expression Editor provides a list of subquery comparisons under the WHERE/HAVING section of the Function list.

Contributed by Sheryl Quinlan, Technical Consultant

This entry was posted on Wednesday, May 7th, 2008 at 8:40 am and is filed under Home, SEQUEL. You can follow any responses to this entry through the RSS 2.0 feed. Responses are currently closed, but you can trackback from your own site.