SQL iQuery

Scripting Tool Preprocessor Examples

#ifdef Example

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.

#ifExists Example

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.

#if Example

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

#rtvdtaara Example

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.

 

<<Directives - More Examples>>