Scripting - SetParm

This function names a User-Defined Parameter and sets a value for it.

Parm. The name of the parameter.
Value The value of the parameter.

Example:

--aqt setparm,parm=schema,value=SYSCAT

--aqt output,file=batch_sample.csv,type=csv,filemode=append

select * from syscat.tables where tabschema ='$schema';

Setting a parameter to a result of a query

You can set the parm to a value returned by a query. Example:

--aqt setparm,parm=yesterday,value=(select current date - 1 day from sysibm.sysdummy1)

If the query returns multiple rows/columns, only the first column of the first row will be used.

Setting a parameter to a value returned by a prior query or stored procedure

You can set a parameter to a value returned by a query, or to an OUT parameter from a stored procedure. This can be useful if you are passing data between a series of SQL statements.

Coding value=$result(3) will set the parm to the 3rd column of the last query run. If the query returned more than one row, the value will come from the last row. If there was no previous query, or the query returned zero rows, the results can be unpredictable.

Coding value=$parm(2) will set the parm to 2nd query parameter (as specified with a Parm function). You would typically use this when the Parm function has specified an Out or Inout parameter, and you have run a stored procedure.

You can embed these functions inside other characters. Example: value=EMP$result(1)00 will return EMPSMITH00 if $result(1) is SMITH.

Setting a parameter to a list of values from an external source

You can set a parameter to a list of values contained in a file (either a csv file or Excel file) or a from a query.

Thiis is described in Scripting - SetParm - $list

Setting a parameter to an Environment Variable

You can set a parameter to an environment variable by coding:

value=$env(CUSTCODE)

In this example, CUSTCODE is an environment variable. When running batch-mode AQT, you can set this in the batch file that invokes AQT.

Setting a parameter to a System value

You can set a parameter Value to one of the following system values.

<date>	The current date
<date(dformat)>	The current date in the format specified by dformat. Has been superseded by <date:format(dformat)>
<time>	The current time in hh:mm:ss format
<timestamp>	The current timestamp in yyyy-mm-dd hh:mm:ss format
<scriptname>	This is only used when running AQT in batch. It gives the name of the script that is being run. For instance if your *.bat file has: `aqt c:\aqt\Customer_Report.sql` then <scriptname> returns `Customer_Report`.
<scriptpath>	Returns the script directory. In the above example, <scriptpath> wiill return `c:\aqt`

Additional options for <date>

You can apply a number of functions to <date> to generate a related date. These are specified as:

<date:function1:function2:function3>

There is no limit on how many functions can be used.

The functions that can be specified are as follows:

startof(datetype)	Start of the date period. datetype is one of h (hour), d (day), w (week), m (month) or y (year). The start of a week is the Sunday. Example: startof(m) will be the first day of the month.
endof(datetype)	End of the date period. dateperiod is as above. The end of a week is Saturday. Example: endof(m) will be the last day of the month.
add(datetype,number)	Adds to the date. datetype is as above number is the number of these to add Example: add(m,1) adds a month to the date
sub(datetype,number)	Similar to add, however the number is subtracted.
format(dformat)	Specifies how the date is to be formatted. If this is not specified yyyy-MM-dd will be used. Note that MM is month, mm is minute. For hour, use HH (24-hour) rather than hh (12-hour) Examples: <date> gives 2021-08-30 <date:format(MMddyyyy)> gives 08302021 <date:format(HHmmss)> gives 204515. <date:format(yyyyMMddHHmmss)> gives 20210830204515
from(datestring)	Converts the datestring to a date.
from(datestring,format)	Converts a datestring to a date, specifying the format of the datestring.

Example 1

<date>

This returns the current date in yyyy-MM-dd format (2021-10-21)

Example 2

<date:startof(y):add(m,3):sub(d,1):format(yyyyMMdd)>

This:

gets the current date (2021-10-21)
goes to the start of the year (2021-01-01)
adds 3 months (2021-04-01)
subtracts one day (2021-03-31)
formats the date as specified (20210331)

Example 3

This shows the use of two connected parms, plus the use of the from function.

--aqt queryparm,parm=sdate,desc=Start Date,select=date

--aqt setparm,parm=edate,value=<date:from($sdate,yyyy-MM-dd):endof(m)>

select * from sales where sale_date between '$sdate' and '$edate'

In this:

queryparm prompts the user for the date, and sets this in parameter sdate
setparm will set parameter edate to the end of the month given by sdate

Date and Time

<date> includes both the current date and time.

startof will return the date with zero hour/minute/second. The exception is startof(h) which will be the current hour with zero minute/second.
endof will return the date with time 1 second before midnight (23:59:59). end(h) will be the last second of the current hour.
Example: endof(m) returns 2021-10-31 23:59:59
To have endof return the date with zero time, follow it with a startof(d).
Example: endof(m):startof(d) returns 2021-10-31 00:00:00
add and sub return the date with the current time.
Example: if the date is currently 2021-10-21 09:44:34 then <date:add(m,1)> returns 2021-11-21 09:44:34
Use the format function to specify whether you want the date / time or timestamp value returned.
<date:format(HH:mm:ss)> does the same as <time>

Setting a Parameter to an AQT Option

You can set the parameter to an AQT option with:

$option(optionname)

Any AQT option can be used, however the most useful ones are likely to be exportdir (the default export directory) and querydir (the query directory). Examples:

$option(exportdir)
$option(querydir)