In the following example, the variable &DEPARTMENTS is used to include a list of Employee Department codes into the SQL WHERE clause. If the variable is specified on the RUNIQRY command's SETVAR parameter, the supplemental WHERE condition is included in the final SQL statement.
SELECT empName, empDept FROM payroll.empFile WHERE empName <> '' #ifDef &departments and empDept in (&departments) #endif ORDER BY empDept,empName
To running this script and assign a runtime value to the &DEPARTMENTS variable, use this RUNIQRY command:
RUNIQRY SRCMBR(EMPLIST) SRCFILE(MYLIB/QSQLSRC) SETVAR((DEPARTMENTS '19,20,38,39,40'))
Resulting SQL Statement:
SELECT empName, empDept FROM payroll.empFile WHERE empName <> '' and empDept in (19,20,38,39,40) ORDER BY empDept,empName
To run the original script without the &DEPARTMENT, use this RUNIQRY command:
RUNIQRY SRCMBR(EMPLIST) SRCFILE(MYLIB/QSQLSRC)
Resulting SQL Statement:
SELECT empName, empDept FROM payroll.empFile WHERE empName <> '' ORDER BY empDept,empName
Because there was no &DEPARTMENTS variable specified on the SETVAR parameter, the #IFDEF result is false and the supplemental where CLAUSE component is omitted.
Sometimes if a file already exists you want to avoid creating it. the standard SQL CREATE or REPLACE TABLE function is useful for that, but if it already exists, you may not want to "re-create" it. In this example, I use the #ifexists directive to check for a Global Temporary Table and if it does not exist, I create it.
#default RMT = CHICAGO #ifexists SESSION.&RMT_ITEMMAST #status Temp file link for &RMT Item Master already exists... deleting it. DROP TABLE QTEMP.&RMT_ITEMMAST; #endif #status Creating temp file for &RMT MASTR. Standby... DECLARE GLOBAL TEMPORARY TABLE SESSION.&RMT_ITEMMAST as ( SELECT m.item,m.description,m.price,i.qtyoh,i.qtysold FROM &RMT.ORDERS.ITEMMAST M INNER JOIN &RMT.ORDERS.INVENTRY I ON m.item = i.item WHERE m.ACTIVE <> 'D' ) WITH DATA
The special SQL SESSION schema is an alias for the QTEMP library. It happens to be where GLOBAL TEMPORARY TABLES are created. The only valid qualification is QTEMP or SESSION.
The #ifexists checks for the file CHICAGO_ITEMMAST in QTEMP (in the SESSION SCHEMA) and if it exists, it simply sends a *STATUS message that the file already exists and then deletes (DROPs") the file from QTEMP. Then it creates the table using data from the remote CHICAGO database. The 3-level naming &RMT.ORDERS.ITEMMAST is converted to CHICAGO.ORDERS.ITEMMAST by the iQuery preprocessor. The GTT that is created is named CHICAGO_ITEMMAST. Note the "WITH DATA" clause that causes the remove file's data to be pulled into this file during the creation process, based on the WHERE clause. Basically this is a big CPYF (Copy file) statement that copies a file from a remote server to the local system.
The #ifexists directive can be used to test for any object type. To do so, specify the object type after the object name (separated by a space), for example:
#ifexist QGPL/iQueryDbg *DTAARA
This returns true if the data area named IQUERYDBG exists in library QGPL.
Note that qualified object syntax may be CL or SQL style, that is lib/object or lib.object format. You cannot test for S/36E style file names that contain a period in their file name.
To condition whether the next set of statements are included, the #if directive may be used.
The #if directive allows users to specify a conditional expression that is evaluated at runtime. The conditional expression can contain mathematical formulas, iQuery variables, and literals.
#if &credit = 0 #undef &maxcredit #else #define &maxcredit = &credit #endif SELECT * FROM QIWS.QCUSTCDT #ifdef &maxcredit WHERE c.cdtlmt >= &maxcredit #endif order by C.cusnum
In the next example the credit limit is stored as 100's but we want the user to specify a value of 1 to 99. So we multiple that value to achieve the true credit limit value.
#define &maxlimit = 9999 #if &credit * 100 <= &maxLimit #define &maxcredit = &credit #else #undef &maxcredit #endif SELECT * FROM QIWS.QCUSTCDT #ifdef &maxcredit WHERE c.cdtlmt >= &maxcredit * 100 #endif order by C.cusnum
The retrieve data area (#rtvdtaara) directive reads the designated data area and stores its value in the iQuery session variable. Any type of data area is support, *CHAR, *DEC, or *LGL.
#default &ordnbr = 0 #ifExists qgpl/salestax *dtaara #rtvdtaara &TAXRATE = qgpl/salestax #msg Using tax rate &taxrate #eval &TAXRATE = &TAXRATE / 100 #else #eval &TAXRATE = 7.5 / 100 -- If no data area then use 7.5% #endif SELECT item,price,dec(price* &taxrate,7,2) as "Tax", dec(price + (price * &taxrate),7,2) as "Ext" FROM orderDetl WHERE ordNbr = &ordnbr order by lineNo
In this example, the SALESTAX data area in QGPL is used to store the sales tax rate. The rate is stores as a Dec(7,2) value so to convert it into a percentage, we divide it by 100. If there is no SALESTAX data area, the default tax rate of 7.5% is applied.