Previous Topic

Next Topic

Book Contents

Book Index

Using $list

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.

Delim

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

Examples

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'