You can define a number of User-Defined Parameters that can be used throughout AQT.
User-Defined Parameters are not to be confused with substitutional parameters described in Adding parameters to a query. The use of ? in an SQL statement to indicate a substitutional parameter is a standard part of the SQL interface to your database. The query (complete with ?s) is passed to your database for processing. The database responds to AQT with the numbers (and types) of the parameter values that need to be supplied. AQT will prompt you for these.
The use of substitutional parameters has a number of advantages:
There are some disadvantages of substitutional parameters:
User-defined parameters provide an alternate mechanism for using parameters in your SQL.
Parameters are indicated with a $ followed by the parameter name. Example:
select * from customers where cust_code = '$custcode'
Note that, unlike substitutional parameters, you need to enclose string values in quotes.
For batch-mode AQT, User-defined Parameters are always enabled.
For online AQT, User-defined Parameters are (by default) not enabled. You can enable this by (from the Run SQL window), going Tools > Parameters, then checking Enable Parameters. Click on OK. This setting is saved between sessions, so you only have to do this once.
On the User-defined Parameters window, you can also specify whether parameters are to be indicated with a $ or a colon. This is done with the Indicate parameters with option. By default, this will be a $. The only choices are $ or colon.
Parameters can be defined in three ways:
If Options > General > Remeber user-parms between sessions is selected, AQT will save the parameter values beetween sessions.
The file they are saved in is parms.txt in your Default Directory. The parms are saved whenever AQT shuts down successfully, or when OK is clicked on the User-Defined Parameters window.
Parameters are often used when running batch scripts. Any changes to parameters done during batch scripts are not saved.
A parameter can reference parameter.
In this, the value of rundate is the name of another parameter.
When you specify:
select * from accounts where transaction_date > '$rundate'
the following will be run:
select * from accounts where transaction_date > '2007-03-31'
Using the function Tools > Show SQL after parameter replacement is useful for seeing how parameters will be resolved.
You can delete parameters with the delparm statement. You might wish to do this at the start of a batch script. Examples:
--aqt delparm,parm=custcode (deletes a single parameter)
--aqt delparm,parm=*cust* (deletes all parms containing cust)
--aqt delparm,parm=* (deletes all parameters)