IQU - Example
In this example script we have used IQU as a simple reporting solution by generating an Excel file.
// When the output is suppressed the results of queries will not
// be displayed on the screen. You can use this to optimize the
// execution speed of large scripts. When speed is not an issue
// leave the output enabled for debugging purposes.
SET SUPPRESS_OUTPUT ON;
// Sets the parameters :Customer and :Start_date. These parameters
// will be used thoughout the script.
SET PARAMETER Customer 59;
SET PARAMETER Start_date '1/1/2005';
// Set the root directory where IQU can find databases and scripts.
SET ROOT_DIRECTORY 'C:\Data';
// Connect to the database. The path 'c:\data' will be added as a
// result of the SET ROOT_DIRECTORY statement.
CONNECT 'Logistics.fdb' USER 'SYSDBA' PASSWORD 'masterkey';
// Create a new Excel file. The customer parameter is used to
// generate a custom file name.
XLS CREATE 'C:\Temp\Monthly Statistics for :Customer.xls';
// Create a sheet in the Excel file with the name 'Details'. If
// you omit this statement a default sheet will be created as
// soon as the first result is exported to the Excel file.
XLS SHEET Details;
// Create a header (bold with larger font) in the Excel sheet.
XLS HEADER Action detail report;
// Write a line of tekst to the Excel sheet. This text will be
// written on a separate line right under the header.
XLS ECHO This line will be put into the Excel sheet.;
// You can also use the normal echo. The XLS variant was only
// added to explicitly indicate you are writing to the XLS file.
ECHO This line will be put into the Excel sheet.;
// Select some result from your database. The output will be
// written as a table to the Excel sheet. Note: We have used the
// :Start_date and :Customer parameter in this query. If you did
// not set the values IQU will ask their values.
ID, ActionType, Description, DateTime_Action
DateTime_Action >= :Start_date AND
Customer = :Customer;
// Stop capturing query results. The results of queries will no
// longer be written to the Excel file.
XLS CAPTURE OFF;
// Suppress errors and try to delete the 'Temp' table. When the
// 'Temp' table is not present errors will be ignored and the
// script will continue.
SET SUPPRESS_ERROR ON;
DROP TABLE Temp;
SET SUPPRESS_ERROR OFF;
// Create a new temp table
CREATE TABLE Temp (
ActionType ActionTypeCode NOT NULL,
YearMonth YearMonth NOT NULL,
CONSTRAINT PK_Temp PRIMARY KEY (ActionType, YearMonth)
// Execute a script to derive the contents of the 'Temp' table.
// The path 'c:\data' will be added as a result of the SET
// ROOT_DIRECTORY statement.
EXECUTE SCRIPT Derive_Temp_Table.sql;
XLS CAPTURE ON;
// Create a new sheet
XLS SHEET Summary;
// Add a header
XLS HEADER Action summary report;
// Output the query result
ActionType, YearMonth, Count(*)
// Move the cursor down with two rows
XLS MOVE ROW 2;
// Perform another select (etc)
/* This is comment as well
// Close the Excel file.
// Execute some program (might change the generated Excel file).
EXECUTE PROGRAM C:\Program Files\Report_Generator\RPGEN.EXE
'C:\Temp\Monthly Statistics for :Customer.xls';
// Reactivate output.
SET SUPPRESS_OUTPUT OFF;