|
|
|
|
|
n,Walker,The $list function of SetParm allows you to set the parameter to a list of values. The list of values can be read from either:
You can then use this parameter in an IN statement. Example:
--aqt setparm,parm=custlist,value=$list(xlfile="C:\AQT\Data\testdata.xlsx",sheet=customers,startrow=2)
select * from customers where custcode in ($custlist)
The format of the $list function is:
$list(parm=value,parm=value,parm=value)
Where the following parms can be specified:
Parm |
Description |
sql |
The query to be run to get the list of values |
db |
The database the query is to be run against. You need to be signed onto this database. This is optional - if not specified it is the current database. |
file |
The name of the file to read to get the list of values. This must be a delimited file |
filedelim |
This is the delimiter used to delim variables in the file. Optional - if not given will default to comma. |
xlfile |
The name of the Excel file to read to get the list of values. |
sheet |
The name of the Excel sheet name |
colno |
The column number in the query/file/spreadsheet of the data for this list. Optional - if not given will default to 1. |
quote |
Whether (y or n) the values in the list will be enclosed in quotes. You would specify this as y if your list of character values, n if it of numeric values. Optional - if not given will default to y. |
delim |
Delimiter used to delimit values in the list. Generally will be a comma. Optional - if nopt give will default to comma. |
startrow |
The row in the query/file/worksheet to start to get the values from. If your file / worksheet has a header row, you should specify startrow of 2. Optional - if not given will default to 1. |
maxrows |
The maximum number of values to get for the list. Optional - if not specified will default to 0 (no limit). |
vals |
Supplies a list of value. The values are delimited with the delimiter specified with valdelim. |
valdelim |
The delimiter used with the vals list. |
Parameter values should be enclosed in double-quotes if they contain any special characters such as comma or brackets.
One of sql, file or xlfile must be specified.
Both delim and filedelim can take the following values:
comma |
, |
comma |
tab |
|
tab character |
squote |
' |
single quote |
dquote |
" |
double quote |
lbrak |
( |
left bracket |
rbrak |
) |
right bracket |
dot |
. |
fullstop / period |
semi |
; |
semicolon |
colon |
: |
colon |
The following statement gets a list of values from a query.
By coding the db parameter this query can run against another database. This provides a way of combining data from multiple databases.
--aqt setparm,parm=custlist,value=$list(sql="select cust_code from order_details",db=testdb)
select * from Customer_Details where cust_code in ($custlist)
This example gets the data from a csv file:
$list(file="C:\AQT\Data\customer_details.csv",filedelim=comma,colno=2,startrow=2,quote=y,maxrows=100)
The following example shows the use of the vals parm.
$list(vals="Smith;Jones;Thompson;Walker;Brown;Spenser",valdelim=semi)
The result is:
'Smith','Jones','Thompson','Walker','Brown,'Spenser'