System i users and experts will meet June 17 - 20

This summer, Help/Systems, the world’s leader in System i Software Solutions, will host Camp Help/Systems, an opportunity for System i users to learn more about automated operations and business intelligence. Scheduled for June 17-20, 2008, at the Oak Ridge Hotel and Conference Center in Chaska, Minnesota, Camp Help/Systems offers users the most current System i information in a friendly and fun environment.

Camp Help/Systems brings together a unique mix of technical professionals and popular speakers. Users who attend can choose from a variety of sessions on a wide range of topics, including the latest in System i news from IBM experts, techniques for using Help/Systems products, and new ways to integrate their professional and personal lives. Among those presenting are Dr. Frank Soltis, the creator of the System i architecture; Richard Dolewski, an expert in disaster recovery; and Kent Milligan, a specialist in System i database topics. Users also can enjoy the antics of Stevie Ray, a well-known improv comedian and corporate trainer.

In addition, users have the opportunity to meet with other attendees and learn from their experiences.

To augment the Camp Help/Systems experience, users can take two full days of pre-camp workshops on June 16 and 17 that will provide in-depth training on Robot/SCHEDULE, Robot/CONSOLE, and SEQUEL.

Camp Help/Systems reflects Help/Systems’ ongoing commitment to the education of the System i community. For more information, contact Katie Carnicom at 1-800-328-1000. Or, visit www.helpsystems.com/education/camp/ to download a brochure or register online.

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

Is there a report that shows my Robot/SCHEDULE security setup?
Yes, if you are running Robot/SCHEDULE 9.25 (or higher). Here’s how:

* Make sure the ROBOTLIB library is in your library list

* Go to a command line and enter CALL RBT400

This command generates a report listing Robot/SCHEDULE’s current security setup.

How can I tell which libraries and objects Robot/SAVE is encrypting?
It’s easy:

1. Go to the Robot/SAVE Main Menu and select option 4 (Reports Menu).

2. When the Reports Menu displays, select option 1 (System Reports).

3. Print the report titled “Library and IFS Objects Selected for Encryption Report.”

Robot/SAVE generates a spooled file (RBS441P) listing all libraries and objects that are either set up for encryption globally, or are encrypted only for a specific backup set.

Can Robot/SPACE generate a list of duplicate objects on my system?
Yes. Create a Storage Audit for all libraries and objects. Add the task “List Duplicate Library Objects.” Make sure you select “Print Results of this task” to create a spooled file with the results.

How can I tell SEQUEL to calculate the last day of the current month?
Use this statement:

CVTDATE(YEAR(Current Date),MONTH(Current Date),01)+1 month - 1 day

Updated! Are Help/Systems products compatible with i5/OS V6R1?
Help/Systems is committed to making its products compatible with the new release of i5/OS. We began shipping V6R1-compatible code for most products at the end of April 2008. For more information, download the new Help/FACTS, “Compatibility information for Help/Systems products moving to i5/OS V6R1 (IBM i 6.1)” or contact technical support.