IQU - Example

// 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.

SELECT

   ID, ActionType, Description, DateTime_Action

FROM

   Actions

WHERE

   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

SELECT

   ActionType, YearMonth, Count(*)

FROM

   Temp

GROUP BY

   1,2;

 

// Move the cursor down with two rows

XLS MOVE ROW 2;

 

// Perform another select (etc)

/* This is comment as well

SELECT

   ...

*/

 

// Close the Excel file.

XLS CLOSE;

 

// 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;