Scripting - Queryparm

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

• Parm

  The name of the parameter.

• Desc

  The display-name for the parameter. When the user is queried for the parameter-value, Desc is used to describe the parameter. Desc is optional and, if not specified, the same value as Parm will be used.

• Pwd

  If the parameter is non-display field, code pwd=y. When the parameter is entered by the user it is displayed as asterisks. You would use this option when prompting for a password to be entered.

  The Pwd parameter is not supported when a dropdown list is used (see the next section).

• Select

  This allows you to specify either:

  • a date-picker, for easy selection of a date / time or timestamp value
  • a dropdown list of values to user is able to select from
  • a file for the user to select

  The Select parameter is discussed later.

• Freetext

  When a dropdown box is displayed, whether the user can enter text into the textbox.

  When False, the user can only select items from the dropdown list.

  The default is True.

• Value

  The value the parameter will be set to. If not specified, it will take the existing value of the parameter.

  The values you can code here are the same as for Setparm. In addition, if you have specified a dropdown list, you can specify a value of <n>. The parameter will be set to the nth value from the dropdown list.

• IDParm

  The name of a hidden parameter. Descripted later.

• FileDir

  When the user is being prompted for a file, this gives the start directory for the file dialog.

• FileType

  When the user is being prompted for a file, this gives the file types of the file being prompted for.

  FileType is specified as a series of entries seperated by a pipe (eg. vertical bar). The format is: description | filemask | description | file mask.

  Examples:

  • All files (*.*)|*.*
  • csv files|*.csv|Text Files|*.txt
  • customer exports|cust*.csv

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

• parameters are case sensitive. You must refer to the parameters with the same case as you define them (in the above example it will find $user-id but not $user-id) .
• up to 10 parameters can be specified.
• parameter replacement is fairly dumb. If you have parameters cust (=SMITH) and custcode (=JONES); coding $custcode will give you SMITHcode rather than JONES.
• queryparm requires screen input from the user. It should not be used for unattended operation.

Select

This allows you to specify:

• a date picker field where the user can select a date / time or timestamp value.
• a dropdown list of values; the user can select one of these values.

To specify a date picker, code one of:

• select=date
• select=time
• select=timestamp

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

• select=fileopen
• select=filesave

To specify a drop down list, specify either:

• select=(comma-separated list of values)

  The values should be enclosed in double-quotes if they contain any special characters such as commas, spaces or brackets.

• select=(select statement)

  The select statement is run to populate the drop-down list.

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:

• the first column (customer_name) is the set of values the user will be shown
• the second column (customer_id ) is a "hidden" value

When the user selects a value from the dropdown list:

• the main parameter (custname) is set to the value the user selected
• the idparm parameter (custno) is set to the value of the hidden value

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.

Advanced Query Tool

https://www.querytool.com

© 2023 Cardett Associates Ltd. All rights reserved.