SEQUEL contains many unique and powerful functions to enhance System i data access. It helps you complete tasks that are simply not possible with Query/400 or other third-party data access tools.

• Scripting
• Date Conversion
• Looping / Iterative Processing
• Time- and Resource-Saving Functions
• Database Operations and Data Reformatting
• J. D. Edwards Database Conversion
• Convert Query to SEQUEL Objects

Scripting lets you run multiple SEQUEL commands (and most other system commands) together. Scripting gives users much of the capability of Command Language (CL) without requiring technical programming knowledge.

The Scripting interface (below) allows users to enter commands on sequentially numbered command lines. The Script is then saved as a new object and can be run from a desktop shortcut, ViewPoint Explorer, command line, job scheduler, or web browser (using the SEQUEL Web Interface). Runtime variables from a single prompt are available to every command used in the script. Step mode execution runs a script, one step at a time, to facilitate troubleshooting.

Scripting Interface (click image for larger view)

SEQUEL's date conversion functions simplify date conversion and comparison operations. SEQUEL can convert existing single- or multi-field dates from packed, zoned or character formats to the i5/OS date data type. It lets you:

• Present a date field in as many as 10 different date formats
• Compare different date values regardless of format (MDY vs. YMD, etc.)
• Determine the difference (in days) between two date values with simple subtraction
• Determine end of month or end of year through the addition or subtraction of days, months, and/or years
• Order database records according to a date value without regard to its format

The Run Command (RUNCMD) list processor enables iterative processing based upon data extracted with a SEQUEL view. For instance, RUNCMD may include a SEQUEL view that uses conditional statements to extract a subset of customer numbers and related data from a System i database file. RUNCMD then will process a series of commands, programs or a SEQUEL Script for each customer. Each iteration of RUNCMD can receive field values from the original SEQUEL view as substitution variables.

RUNCMD provides a good way to automate complex, multi-step processes that require customized output, such as invoices or other customer-specific statements that are sent via e-mail broadcast in the same operation. Another use would be to process output using multi-member physical files with variable layouts.

SEQUEL helps you get your job done faster. It can perform in one step what other tools require multiple query passes and intermediate files to accomplish. SEQUEL lets you:

• Build subqueries (a query within a query)
• Merge records with the UNION clause
• SORT and select records based on a derived field
• SORT and select records based on a summarized field
• Summarize on a derived field
• JOIN files with a derived field
• JOIN a file to itself

In addition to standard arithmetic and trigonometric operations, SEQUEL supports:

• Date, time, and timestamp data types
• Conversion of existing fields to date data types
• Conversion of character to numeric data · Conversion of zoned numeric to character data
• Uppercase conversion
• Hexadecimal conversion
• Logical (bitwise) manipulation
• Record level functions (MIN, MAX)
• Character string operations (CAT, BCAT, TCAT, STRIP, TRIM, SUBSTR)
• Variable length character fields
• ROWID (to access a relative record number)
• USERID access
• CURRENT DATE access

Now you can access J. D. Edwards (JDE) database information directly with SEQUEL, Query/400, PC spreadsheets, and other 3rd-party reporting tools. SEQUEL includes a conversion function that simplifies the use of data from JDE application database files and improves end user access to data. SEQUEL automatically links field attribute information from JDE data dictionary files to transaction files and creates a new view of the data. The original dictionary and database files are not changed in any way. A single command needs to be run once, when SEQUEL is first installed, or whenever the data dictionary files are changed. Once that's done, it's a simple matter for users of SEQUEL, Query/400, spreadsheet and other third-party reporting tools to obtain meaningful information without the hassle of having to translate decimal positions or decipher cryptic field descriptions.

SEQUEL ViewPoint contains an Import function that lets you convert query definitions (*QRYDFN), Query Managment queries (*QMQRY), and Query Management forms (*QMFORM) to SEQUEL views and/or reports. It can convert queries selected by name, generic name, or even all queries in one or more libraries into views and reports in a library of your choice.

See also:

Video - Query conversion feature.

Free Email Sign-Up

To get the latest operations automation and business intelligence news, sign up for Robot Direct by entering your e-mail address. We'll let you know about site updates or breaking news about twice a month!