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.

SEQUEL views can be regrouped or drilled into to provide new insights

SEQUEL ViewPoint allows you to view the data in your databases in different ways. When you create a view by using a function that groups (summarizes) data, you automatically create views that can be “drilled into” to see the supporting detail. Views that include functions, such as SUM(), MIN(), MAX, COUNT(), or AVG() let you drill into the displayed results.

Note: This feature requires fields to be added to the Dynamic Drill Down Field list. This list is stored in a file (SEQUEL/VPTDYNFLTR) that you can maintain easily using ViewPoint Administrator, or a green screen command line with UPDATE, INSERT, or DFU.

Drilling down from the summary level to the detail is easy. When you right-click on a result, you can select from two options: Regroup or Show details.

Regroup
The Regroup option lets you sort the entire result set and re-summarize it by one of the many fields. You can run this process repeatedly without any changes to the view.

The Regroup Data window lists all fields from all files used in the view that are also defined in the Dynamic Drill Down Field List. If no fields from the starting view exist in the Dynamic Drill Down List, a dialog displays allowing you to list all fields for the view. Just place a check next to all the fields for which you want to regroup the data.

Right-click on BMXCARTON and choose to regroup the data.

If you choose to regroup using the state field (left) and press the OK button, a second window opens displaying the new results (right).

Show Detail
This option shows the supporting detail for the selected grouped record, allowing you to drill-into the data that created the summary information. Right-click on a specific line, select “show details,” and select the data you want to see from the detail of that line.

Again, you can run this process repeatedly without any changes to the view.

In this example, right-click on Illinois and choose “Show details” and name to display customer detail.

SEQUEL filters the original data set to display customers in Illinois.

Contributed by Carlo Panzarella, Technical Consultant

Are Help/Systems products compatible with i5/OS V6R1?
Help/Systems is committed to making our 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.

Does Help/Systems hold a User Conference?
Yes, Help/Systems is hosting a User Conference June 17-20, 2008 in Minnesota. If you haven’t received a brochure in the mail, visit www.helpsystems.com/education/camp/ to download a copy and register online.

Are hyperlinks supported in my ESEND e-mail messages?
Yes, if the message type is *HTML. Here are some formatting tips:

• E-mail links: Use mailto:myname@mydomain.com or just myname@mydomain.com.
• Web site links: Use http://www.helpsystems.com or just www.helpsystems.com.
• Document links: Enter the complete HTML reference <A href=”http://www.helpsystems.com/intel/video.html”>a SEQUEL movie page</A>. This will display as a SEQUEL movie page in your e-mail.

I am trying to move a Microsoft Access project to SEQUEL ViewPoint and I am stuck on one formula. In Access, I use this formula to add a trailing 00001 to a trimmed field:

Left([eqmfsn],4) & “00001″

How would I do this in Viewpoint?

Here’s a good way to handle this in Viewpoint:

SELECT DECIMAL(CAT(SST(CHAR(cusno),1,6), CHAR(’00001′)),15,2) name(calc1), cusno*100000+1 LEN(15,2) name(calc2)

FROM sequelex/custmast

Just replace the name variables with your own file names.

Lansing School District in Lansing, Michigan, is made up of over 16,000 students and covers three counties in the mid-Michigan area. They are the largest school district in the tri-county area with 27 elementary schools, four middle schools, and three high schools. They currently have nine magnet schools within their district and many specialty programs for children and adults.

SEQUEL supports the “data to the desktop” initiative
Steve Maiville, a supervisor in the Technology Department, explains the many ways Lansing uses SEQUEL. “Our application software is CIMS, which has traditionally been a 5250 green-screen application. We’ve been using SEQUEL for about seven years now. Ironically, we originally purchased SEQUEL to allow us to format three-up labels. But we’ve gone way beyond the simple report formatting that labels require. Our district has started an initiative we call ‘Data to the Desktop.’ The goal is to make data available to the people who need it at their desktop, often in real-time. This includes people who, in many cases, have never had real-time access to this data before. SEQUEL is the tool that allows us to make this happen quickly and efficiently.

“We’ve even been able to extend data to bus drivers in our transportation department using the SEQUEL Web Interface. They can access information about the students on their bus routes. This includes pictures of the students, parent phone numbers, e-mail addresses, and emergency contact information, as well as information about any special needs that a student may have, such as asthma or allergies to bee stings.”

SEQUEL dashboards aid administrators
Steve has found SEQUEL executive dashboards to be effective for displaying information. He explains, “We use SEQUEL dashboards to enable administrative and support people to see student information on discipline, testing, and attendance. We integrate run time prompting into our dashboards. CIMS stores data for different years in different file members on the same file. Depending on the prompt value, we can have the dashboard automatically assign the appropriate library and members to use when opening the files. We pass the library and member names from one view to the next with the drill downs. By using this practice, we can compare our staffing this year with past years. With SEQUEL, I’ve been able to greatly simplify the data extraction process.

“We also automated the process of distributing the monthly budget reports using SEQUEL. Previously, the staff of the Finance Department would print thousands of pages of monthly reports which they would separate and mail to different schools and departments. It was very costly and time-consuming. Now, we use the RUNCMD command in a SEQUEL script to automatically e-mail monthly formatted budget reports, as PDF attachments, to each of our schools and departments. Each PDF report has data specific to the recipient. I have a simple file with a record for each recipient, and fields for department or school number(s), a salutation, and an e-mail address. This file drives the whole process. It has saved us a lot of time and money, and is very easy to modify.”

SEQUEL helps speed purchasing
In the Lansing district, purchase orders normally have to be approved at multiple levels. Steve describes the process, “Twice a week we run a process using SEQUEL to examine all unapproved purchase orders (POs), determine who is next in line to approve or disapprove each PO, and e-mail that person reminding them that they need to take action so that the approval process can proceed.”

SEQUEL provides information in real time
Steve continues, “With SEQUEL, I can provide teachers with real-time information on their students through a Web browser. They are really excited, using words like ‘empowered’, and coming up with good ideas on how to improve the system. Currently, they can access information about how to contact parents, which allergies a student may have, assessment test scores, grades—whatever they might need.”

SEQUEL assists with data analysis
Lansing School District takes advantage of SEQUEL’s analysis functions in multiple ways. Says Steve, “We are also using SEQUEL to analyze data in the aggregate. Teachers and administrators can look at averages and summaries for test scores, days absent, sick days, grades, and so on. They can drill down any number of ways to see the supporting detail. Currently, our testing and research people are working with me to identify ways to streamline the data collection needs of the district.

“We also use SEQUEL’s date manipulation capabilities extensively. For example, we have blood drives where students, who will reach the age of 17 by the date of the drive, can volunteer to donate. SEQUEL subtracts their date of birth from the eligible date and creates letters to send to the students who qualify. The date and the age they need to be on that date are passed as run time variables. I really like the way I can use date arithmetic in my decision logic.”

SEQUEL helps simplify and improve things
Steve sums up the power and usefulness of SEQUEL. “I am always looking for ways to simplify and improve things. SEQUEL is the perfect tool for the job. I have had the opportunity to call the support staff on many occasions, and they are wonderful to work with. I love SEQUEL—it lets me solve problems creatively!”

Can Robot/ALERT send text messages to cell phones?
Yes. Use Robot/GUIDE to help you set up a TCP/IP vendor. It works just like sending e-mail to any e-mail address with Robot/ALERT.

I have a job that runs every day. On certain days when the job completes, I want a Robot/SCHEDULE job to react to its completion. Can I create a reactive job and specify which days I want it to run?
If you have a reactive job and days to run specified, Robot/SCHEDULE looks for both conditions before it runs the reactive job. Therefore, if the prerequisite job completes and it is a day the reactive job is scheduled to run, Robot/SCHEDULE runs it.

If the prerequisite job does not run, the reactive job will not run. If the reactive job has a day and time specified, and the prerequisite job has not run, you get a W (warning) status on the reactive job indicating that, “Not all prerequisites have been met.”

How do I make a script run when I open a SEQUEL dashboard?
There are two ways to make a script run in a SEQUEL dashboard: automatically, or by pressing a button. Either way, you must add an Action Button to your dashboard.

1. Create a new dashboard or open an existing dashboard in design mode.

2. Select Insert > Action Button from the menu.

3. In the Action Button dialog, press the SEQUEL Object button and navigate to your script.

4. To make the script run when the dashboard is opened, check the “Autorun” box. (If this box is not checked, you must press the Action Button to run the script.)

How do your queries access Oracle, MySQL, SQL, and other databases?

Do you need to combine System i, UNIX, and Windows data all on one executive dashboard panel? Do you need to consolidate data from multiple databases in your network? Are you tired of being forced to transfer System i data from your DB2/400 database to a data warehouse so that it can be queried?

The basic i5/OS Query product allows you to access only your local DB2 data through the queries you write. IBM’s new Web Query tool also allows you to access your DB2 data, but it costs extra to use and connect to other databases. In addition, you have to go somewhere else for support for these connections. Other tools allow you to access DB2 data on your System i, but they do it from Windows or UNIX. Wouldn’t it be nice to have a System i-centered solution?

SEQUEL connects to remote databases—FREE
SEQUEL allows you to access DB2/400 databases, along with access to Oracle, MySQL, Microsoft SQL Server, and other JDBC type 4 databases. SEQUEL makes it easy to create connections to these databases. Best of all, end users never have to know that they are viewing data from another system, or building a dashboard that presents data from multiple sources.

For example, you can combine data from Oracle, SQL Server, and DB2 into a single executive view (see Figure 1). With SEQUEL, your team can access other servers for no additional charge.

How easy is it to connect?
The SEQUEL Administrator makes it easy to set up connections to other databases. You just enter the system name, or IP address, along with a user ID and password. Once the connection has been configured, any

SEQUEL user can manually point to the defined database (see Figure 2). Or, users can set up their defaults so they always access the selected database. With SEQUEL, users can simply build a view and choose the files they want to use, without worrying where they are located.

How easy is it to add tables?
To see a list of tables you can query, simply click on the file list (see Figure 3). Just as you can with DB2 files, it’s easy to select the tables you need to create new views, tables, drill downs, summarized graphs, reports, and more from your non-System i data.

Our support team can show you how easy it is to use SEQUEL to solve your database access issues. Call us at 1-800-328-1000 to request a live demo or a FREE 30-day trial in your own network of databases. Or, you can download and view an archived presentation on this topic.

Contributed by Tom Huntington, Vice President of Technical Services

All my SEQUEL reports are printing duplex. How do I change that?
You can turn off duplex printing by changing the SEQUEL/SQLPRT1 printer file. Use the following Change Print File (CHGPRTF) command:

CHGPRTF FILE(SEQUEL/SQLPRT1) DUPLEX(*NO)

I want to use Robot/ALERT to send e-mail messages, but my System i is not configured as an e-mail server. Is there a way around this?
Your System i doesn’t have to be an e-mail server for Robot/ALERT to send e-mail messages. If you use Robot/GUIDE for Automated Vendor setup, you simply specify the domain name or IP address of your SMTP e-mail server in the Outbound TCP/IP Host field. If you are using a two-way e-mail connection, specify the domain name or IP address of your POP3 e-mail server in the Inbound TCP/IP Host field.

I use Robot/SCHEDULE. When I set up a reactive job, it runs as soon as it gets the completion code for its prerequisite job. I would like the reactive job to wait a little while before it runs. How would I set this up?
You can attach OPAL code to the reactive job to do this. The OPAL must be submitted before the reactive job runs. Here’s an example that would delay the job for one minute. This code checks that the reactive job is not in a delayed status and then adds one minute until it will be submitted.

SEQUEL ViewPoint provides grid control features that let you format your view results. You can:

• Choose contrasting colors for even/odd result rows
• Format different aspects of your results (such as headings, rows, and selection area) distinctively, including font attributes (style, size, weight, and color) and foreground/background colors.
• Use conditional formatting to highlight exceptions
• Use the Fast Group-and-Sum function for non-summarized data

Let’s look at how you might use some of these capabilities. Two sample order files, ORDHEAD and ORDLINE, are shipped with SEQUEL. Using these files, you can create a very simple SEQUEL view with the following statement:

SELECT cusno.1, ordno.1, prdno.2, quano.2, actsp.2, quano*actsp NAME(extprc)
COLHDG(”Extended” “Price”)
FROM sequelex/ordhead,sequelex/ordline
INNER JOIN ordno.1=ordno.2
ORDER BY cusno

Then, you can use ViewPoint to format the results. In this example, we grouped the results for each customer number, even though the statements creating the view do not contain any group or sum syntax.

Set user/desktop defaults
You can set default grid formatting at the user/desktop level, or you can apply formatting to a view. To apply formatting to a view, you must define the formatting during a ViewPoint Design View session. ViewPoint saves the formatting as part of the view definition. View formatting overrides the defaults set at the user/desktop level (for that view only).

Format view results
Here’s how you format view results:

1. Open an existing view in a ViewPoint Design View session. If you are creating a new view, do a Save or Save As operation first. Display your results.

2. In the results window, select the Format > Layout option to continue.

3. Open the Layout Format window. Select the General tab to specify whether you want to have alternating colors for even/odd rows and your color selections. Use the grid line drop-down menu to select a grid line format from the selection list.

4. Select the Grouping tab to specify whether you want to enable the automated grouping function. If this option is checked, the results may be grouped as shown in the first example. To use this feature, you simply drag-and-drop a column heading (such as Customer Number) to the top margin of the results window and SEQUEL groups the data by that value.

5. Use the Style Editor tab to pick one of the styles (sections) in the results and specify formatting.

6. If you are using a Design View session, SEQUEL enables the Column Editor tab. Use this tab to specify conditional highlighting for special occurrences of data in the view results. For example, we could highlight a value like a low inventory level. When this value occurs, the amount shows up in red. Use this feature when you are creating a SEQUEL dashboard with key performance indicators.

7. Finally, use the Printing Options tab to apply print settings when sending the results to a network printer.

See SEQUEL ViewPoint’s help text for additional examples and information about grid control options.

Contributed by Steven Smith, Technical Writer

When you use SEQUEL ViewPoint on your system, you can have it track who is using what and when. You can see if access paths are created, which files are queried, and how many records are retrieved. You can do all this with SEQUEL auditing.

What is SEQUEL auditing?
You access auditing through the ViewPoint Administrator. Auditing allows you to monitor your investment in SEQUEL. Its inquiries and reports help you get the most out of SEQUEL by showing you where SEQUEL is used most heavily and how it affects your system’s resources. Using the auditor’s inquiry and analysis tools, you can effectively manage the query environment.

There are three phases to the auditing process: collection, distribution, and analysis. SEQUEL collects data in a System i journal and its attached receiver. You turn on the auditing process through each user’s default data area, to collect auditing data selectively. After you collect some data, you can run the “Manage Audit Data” process to extract data from the receiver and distribute it to files in the audit database. Then, you can analyze the data using summary and detail inquiries and graphs.

Getting started
Data collection and distribution takes place “behind the scenes” and must be done before data analysis. To begin, go into the ViewPoint Administrator and select the SEQUEL Auditing option. Then, follow this easy, three-step process:

1. Choose the Set Audit Default option, to decide which users to audit. Check the box next to each user ID you want to audit.

2. Wait a day or two to allow information to collect.

3. Select the Manage Audit Data option (available only to administrators) to move the data from the journal receiver to the database. (In the future, you can schedule this option to run automatically.)

Once the submitted job completes, you are ready to analyze SEQUEL’s use.

Analysis
You can view the collected data as either summary or detailed information. To display SEQUEL requests by user, object, job name, and access path, choose Show Audit Data. Click User to display summary values of SEQUEL activity for each user you are auditing. Double-click a user to display detailed information about that user.

You can analyze information by object or job name; display a list of files, views, and reports used; or create a list by job name. To display detailed information, just double-click the item. The detail inquiry also shows a command summary that includes a graph.

The Access Path Detail window displays summary-level information about which access paths are used, by whom, and how often. You can drill into the access paths to show the specifics of when access paths were used.

Summary
The SEQUEL ViewPoint Administrator lets you access auditing features that help you keep track of the SEQUEL requests run on your system. It’s an easy way to show your management the importance of your investment in SEQUEL.

Contributed by Sheryl Quinlan, Technical Consultant