Sequel Tips and Tricks: Enhance Your IBM i Queries

Do more with your queries.
June 27, 2016

Working with your IBM i queries is easy—once you have Sequel Data Access.

You can easily convert your queries from Query/400. Then you can enhance them using Sequel.

Not Using Sequel Yet? Try It Today >

In Sequel terms, a query is a view. Your view contains the definition to access your data, and is the basis for other Sequel objects, like reports and tables.

Where do you start when your users request data in a certain way or need a report to include subtotals and possibly some highlighting? What options are there to allow both programmers and end users to be more productive?

Here are our best tips and tricks for being more productive, empowering users, and saving time with your queries.

1. Be Productive with Views

Your users don't need to wait for you, anymore. Today, they can enhance and create queries, aka views, using Sequel Viewpoint. Viewpoint is the graphical user interface (GUI) option for Sequel. 

Existing views can be modified and enhanced any way you want them. Add calculated fields, convert numeric or character date fields into a true date data type field, and add run-time variables.

With run-time prompts, your users will be able to run the same view or report as other users. But they'll be able to select the records they need through the prompted variables and decide how the results should be returned.

Results can be displayed, printed, emailed, transmitted via FTP, and more.

And data can be displayed on the screen, in a dashboard, and in graphs and charts.


2. Empower Users with Data Distribution

Once queries are converted into Sequel objects, your users can display, print, create PC documents, and email the results.

In Sequel Viewpoint, users can quickly view authorized Sequel objects from either the existing or recent tab. And they can run them by double-clicking or right-clicking and selecting a distribution option.


list sequel objects


There are two display options: you can either directly display the results or see the results directly in Microsoft Excel.

Save Results As gives you several ways to create a file in a variety of formats on your PC, IFS (Integrated File System), IBM i, and even send the data to a remote database server.

Send Results distributes the results via email in your desired file type such as PDF or Excel.

Print sends the results directly to a PC printer, or a spooled file can be created by sending the request to an IBM i output queue.

Create Shortcut places an icon on your desktop for single-click access to your data results.


distribution options


3. Save Time with Shortcuts

Shortcuts allow your users to access data when they need it and how they need it. And they won't need to know the object type or what is really running underneath the covers.

Below (figure 2) we created a shortcut of the AR view in the SEQUELEX library by right-clicking and selectingCreate Shortcut to send an email with an Excel attachment.


create sequel shortcut


Then select the distribution option. Below (figure 3) we decided to email the results with an attached Excel document. In figure 4, we named our shortcut and saved it to the desktop for easy access.


set query shortcut parameters

save shortcut parameters


Shortcuts can be sent to users, moved to a mapped drive, and emailed. By clicking the shortcut, the user will be prompted to enter their email information and select attachment type.

email query and reporting results

4. Mastered Basic Views? Make Them Even More Useful!

Administrators and business users alike can modify existing views and reports or create new Sequel objects with ease with Viewpoint.

From the Existing or Recent tab in Viewpoint, you can OPEN a Sequel object in design mode to add calculations, run-time variables, and more, simply by selecting what you need. Sequel offers several functions to enhance your data from converting numeric, character, or Julian dates to a true date data type field to conditional statements where you can imbed logic (IF – THEN – ELSE) into a calculated field.

Example: Using the CUSTLIST view in SEQUELEX, open the view in design mode and select the WHERE clause to bring up the expression editor. Modify the expression by adding a variable to prompt for the state value asCSTTE = &CSTTE. 


query expression editor


At runtime, the user will be prompted to enter the Customer State value. The user can run the same view to collect a list of customers for MN, CA, IL, etc.


data drill down

Giving your users what they need is within your reach.

You can quickly convert Query/400 objects to Sequel objects. And you can enhance your views and reports to convert data to useful information. The results speak for themselves. Your users will be more productive (without asking IT), and everyone will save time. 

Build Your Query Skills

Sign up for training and get more out of Sequel Data Access today. You'll get the essential tips, tricks, and know-how to do anything with Sequel. 

Not using Sequel?

Related Products

Related Solutions