Previous Topic

Next Topic

Book Contents

Book Index

Scripting - Queryparm

This function prompts the user for the values of User-Defined Parameters. The following parameters are available:

Once you define parameters (with either queryparm or setparm), AQT will scan all subsequent statements for reference to the parameters. Parameters are referred to by preceding the parameter name with a $.

Example 1:

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

select * from order_details where order_date > '$sdate'

Example 2:

--aqt queryparm,parm=user-id,desc=user-id,parm=pwd,desc=Password,pwd=y

--aqt connect,dbs=sample,user=$user-id,pwd=$pwd

See the file batch_parms.sql, which is deployed with AQT, for more examples of the use of parameters.

Restrictions

Select

This allows you to specify:

To specify a date picker, code one of:

To prompt the user for a file with the File Open or File Save dialog boxes, specify either:

To specify a drop down list, specify either:

Examples:

--aqt queryparm,parm=sdate,desc="Start Date",select=date,value=<date>

--aqt queryparm,parm=type,desc=Type,select=(T,V,S),value=T

--aqt queryparm,parm=order,desc="Order Number",select=(select distinct order_number from order_details),value=<1>

--aqt queryparm,parm=loadfile,desc="Enter file to load",select=fileopen,filedir="s:\AQT\loadfiles",filetype="Load files|*.csv"

Having a Dropdown list depending on the value of a previous parameter

When your Select statement contains a query, that query can reference a previous parameter.

Example:

--aqt queryparm,parm=Country,desc=Country,select=(Select distinct Country from dbo.DimGeography order by 1)

--aqt queryparm,parm=City,select=(Select distinct City from dbo.DimGeography where Country = '$country' order by 1)

Note that in second parm has $country in the query which populates the dropdown list. $country will be set to the value the user has selected for the Country parm. If a different value of Country is select, the query is rerun and the second dropdown list repopulated.

IDParm

Use this when you want the user to be prompted with one set of values, but pass another ("hidden") value to the query.

For instance, a user may be familiar with the names of their customers, but not the customer ids. In which case you could use:

--aqt queryparm,parm=custname,desc="Customer",idparm=custid,select=(select distinct customer_name, customer_id from customers)

select * from customers where customer_id ='$custid'

Here the select query returns two columns:

When the user selects a value from the dropdown list:

If your select clause is a list of values, you use a colon to delimit the main and hidden values:

--aqt queryparm,parm=type,desc=Type,idparm=typeid,select=(Table:T,View:V,Synonym:S)

This feature only works if you select values from the dropdown list. If you type a value, AQT will not be able to set the idparm value.