One of the neat things in QMF is the ability to specify a kind of hostvariable using so-called &variables, for instance SELECT * FROM MYTABLE WHERE MYKEY = &KEYVALUE. This will make QMF display a pop-up window where you can type in the value for KEYVALUE and get the SQL statement executed using this value. This is not an option in SPUFI or other tools like DSNTIAUL or DSNTEP2 and it has actually bothered me for a long time.

Some time ago I realised that WITH might be able to do the trick. I have only used WITH in connection with recursive SQL and has not thought about other areas where it could be used, but the following will work:

WITH PARM (KEYVALUE) AS 
( SELECT 'A' 
  FROM SYSIBM.SYSDUMMY1 
) 
SELECT * 
FROM MYTABLE, PARM
WHERE MYKEY = PARM.KEYVALUE 

The with part of a SELECT statement builds one or more result tables which can be used in the final SELECT. I only need one hostvariable/parameter in the final SELECT so the trick is to build a table (PARM) containing a single row (SYSIBM.SYSDUMMY1) and a single column (KEYVALUE) representing the hostvarable and assign a value ('A') to this column. If I need to execute the SQL statement again using another value I just alter the value in the first SELECT list.

In order to use the hostvariable/parameter in the final SELECT I have to include the PARM table in the FROM list. If the final SELECT requires more hostvariables/parameters I can append the parameter names after KEYVALUE separated by commas and append the actual values in the first SELECT list. When I execute the SQL many times using SPUFI I only have to worry about changing the hostvaviable values in the first SELECT list. No matter how complicated the final SELECT statement is I do not have to worry about finding and changing the correct values in the correct places. It gets even better if the same value is used several times in the final SELECT. Then I just reuse the PARM.parametername and thus I can be sure that the same value is used in all the right places.