Excel .XLSX Output
Queries may be output in Excel .XLSX format and assigned a sheet name and folder in the IBM i Integrated File System (IFS). When output as .XLSX, the query can translate report break summaries into Excel formulas; protect leading zeros in numeric values like account numbers, inventory codes, and postal codes; and format the detail rows as an Excel table. Excel tables enable users to sort and filter the data and are easy to transfer to Microsoft Power BI.
Extended Record Selection
You may revise the data in a table at any time and reference the same table in multiple queries. When executed, queries that reference a table will select records based on the table’s current contents. You may also write a query so it will prompt users at run-time for the name of the table.
Environment Variables
Query record selections, new fields, sorts, and other instructions may reference values stored as environment variables (EVars). With EVars, you can store values that are not in a database table and pass them to a query. The EVar feature is particularly helpful when you want to pass values (e.g., dates) to a query you’ve specified as the query to run next.
EVars can be created and updated from a simple or conditional new field calculation in a query, through the WRKIQEVAR command in the IBM i 5250 interface, and from IQ Client. A query can process an EVar before the query, as the query reads each record, or after processing the query. You may store an EVar in any IBM i library, including QTEMP.
Email Address Validation
Queries that deliver output via email run through an email address syntax validity checking program (the CHKEMADDR command) at run time. If the program finds an address with an invalid syntax, it continues processing, delivers the query output to the valid email addresses, and adds a message to the query’s job log about the syntax errors found. You may also use this command to test an email address for structural validity.
Email Distribution Lists
Queries may use multiple email distribution lists. You have two new files in the QIQE library to store specific email addresses you want to include (even when they do not pass the syntax checking process) or exclude from all queries and distribution lists. The exclude list is helpful when you are unsure which queries or distribution lists include a specific email address you no longer want to use.
New Field *LIB Resolution
A new field calculation formula may include *LIB if you want to include the current query’s library name in the resulting value.
New Field *NOW Setting
A numeric new field calculation formula may include the system variable *NOW.
FTP Logging and Security
Query job logs include FTP logging. You may use Field Substitution to set your To File and Remote System values. Passwords may be 50 characters maximum, and you can enter port, secure connection, and data protection settings to better secure file transfers.
.CSV Delimiter and Escape Character Handling
Assign your own character and numeric delimiters and escape characters to a query’s .CSV output file to overcome challenges associated with parsing values containing characters like commas and apostrophes.
|
