Was this helpful?
Passing Parameters on the Command Line
You can pass parameters to Report‑Writer from the command line for reports based on SQL queries. You cannot pass parameters that contain delimited identifiers on the command line if your report specification contains a QUEL query.
If necessary, for details on debugging your query, see the section on the II_EMBED_SET printqry option in your System Administrator Guide.
Passing Numeric Variables
Suppose your report specification contains the following code:
.declare deptno = integer
.query select firstname, lastname, deptid from employees
      where deptid = $deptno
To pass in a value for the numeric variable, $deptno, on the command line, enclose the variablename=value clause in parentheses ( ):
Windows and VMS:
report personnel emp (deptno=504)
UNIX:
report personnel emp "(deptno=504)"
Note for QUEL Users
Regardless of whether your report specification uses an SQL or a QUEL query, you pass values for numeric variables in the same way. Your report specification code would look like this for QUEL:
.declare deptno = integer
.query
range of e is emp
retrieve (e.firstname,e.lastname,e.deptid)
      where e.deptid = $deptno
Passing String and Date Variables
The report specification must enclose the string and date variables, $dname and $ddate, in quotes that are appropriate for your query language (in this case, single quotes for SQL), as shown in the following example:
.declare  dname = varchar(20),
          ddate = date
.query select firstname, lastname, deptid from employees
      where deptname = '$dname' and hiredate >= '$ddate'
The previous example retrieves data for employees in the named department, $dname, who were hired on or after a specified date, $ddate.
If the variable is quoted appropriately for your query language (single quotes for SQL or double quotes for QUEL) in the report specification, use the following syntax for specifying a string or date value on the report command line:
report dbname tablename (variablename='value')
Each string or date value must be enclosed in single quotes to identify it to Report‑Writer as a string. Report‑Writer strips off these quotes before assigning the value to the variable in the query. Additionally, enclose the entire variablename=value clause in parentheses ( ). If it contains any characters treated specially by your operating system (such as parentheses in Windows NT and UNIX or slashes in VMS), the parenthetical clause must be enclosed within double quotes to pass it through the operating system.
For example:
report personnel emp "(dname='BL', ddate='01/01/98')"
If the report specification omits the query language‑specific quotes around the variable (for example, where deptname = $dname instead of where deptname = '$dname'), these quotes must be supplied to the query. To do so, include them in the string or date value on the command line within the single quotes required to identify the string to Report‑Writer. You must dereference the embedded single quotes required by SQL. To dereference single quotes within a single‑quoted string you double them (''). Additionally, if the parenthetical clause contains characters special to your operating system, enclose the entire parameter within double quotes. For example:
report personnel emp "(dname='''BL''',
    ddate='''01/01/98''')"
Note for QUEL Users
The report specification must enclose the string and date variables, $dname and $ddate, in double quotes for QUEL, as shown in the following example:
.declare    dname = varchar(20),
            ddate = date
.query
range of e is emp
retrieve     (e.firstname,e.lastname,e.deptid)
      where  e.deptname = "$dname" and 
             e.hiredate >= "$ddate"
This example retrieves data for employees in the named department, $dname, who were hired on or after a specified date, $ddate.
If the report specification omits the query language‑specific quotes around the variable (for example, where e.deptname = $dname as opposed to where e.deptname = "$dname"), these quotes must be supplied to the query by including them within the string or date value on the command line, inside the single quotes that identify the string to Report‑Writer. Additionally, if the parenthetical clause contains characters special to your operating system, enclose the entire parameter within double quotes and escape any double quotes within the parameter according to the rules for your operating system.
Windows: In Windows, you escape double quotes by preceding them with a backslash (\). For example:
report personnel emp "(dname='\"BL\"',
    ddate='\"01/01/91\"')"
UNIX: In UNIX, you escape double quotes by preceding them with a backslash (\). For example:
report personnel emp "(dname='\"BL\"',
    ddate='\"01/01/91\"')"
VMS: In VMS, you escape double quotes by preceding them with another double quote. For example:
report personnel emp "(dname='""BL""',
    ddate='""01/01/91""')"
Passing an Entire Where Clause
In the following examples, the where clause itself is a variable for the report. You pass the entire where clause to Report‑Writer from the operating system command line in the variablename=value clause. The .query section in the report specification might look like this:
.QUERY select * from tablename where $wherevar
In this case, the value in the variablename=value syntax is a string containing the search qualifications. Enclose valuestring in single quotes to identify to Report‑Writer as a string, as shown in the following example:
report accounting recpay (wherevar='valuestring')
If the parenthetical clause contains characters special to your operating system, enclose the entire parameter within double quotes:
report accounting recpay "(wherevar='valuestring')"
The valuestring can include a variable and a value. If the value in the valuestring is a character string or date, it must be enclosed in quotes that are appropriate for your query language (single quotes for SQL). Dereference single quotes within the single‑quoted valuestring by doubling them ('').
For instance, in the following example enclose the date string, 12/31/98, in a double set of single quotes to dereference them inside the quotes surrounding the valuestring:
report accounting recpay
      (wherevar='date = ''12/31/98''')
If your operating system requires it, enclose the entire parameter within double quotes:
report accounting recpay
      "(wherevar='date = ''12/31/98''')"
The resulting valuestring is:
date = '12/31/98'
Note for QUEL Users
The entire where clause is passed to Report‑Writer from the operating system command line in the variablename=valuestring clause. The .query section in the report specification might look like this for QUEL:
.QUERY retrieve tablename.all where $wherevar
The valuestring can include a variable and a value. If the value in the valuestring is a character string or date, it must be enclosed in quotes that are appropriate for your query language (double quotes for QUEL). For instance, in the following example enclose the date string, "12/31/93" in double quotes within the single‑quoted valuestring:
report accounting recpay
         (wherevar='date = "12/31/98"')
If your operating system requires it, enclose the entire parameter within double quotes and escape any embedded double quotes.
Windows: To dereference double quotes in Windows, precede them with a backslash (\). For example:
report accounting recpay
"(wherevar='date = \"12/31/98\"')"
UNIX: To dereference double quotes in UNIX, precede them with a backslash (\). For example:
report accounting recpay
   "(wherevar='date = \"12/31/98\"')"
VMS: To dereference double quotes in VMS, double them. For example:
report accounting recpay
   "(wherevar='date = ""12/31/98""')"
The resulting valuestring is:
date = "12/31/98"
Passing Multiple Parameters
To pass multiple parameters in a where clause, you follow the same rules for the use of parentheses and quotes as described in the Passing an Entire Where Clause section. For example, suppose your report specification contains the following code:
.declare wherevar = varchar(100)
.query select * from employees
      where $wherevar
You would enter the following command on the command line:
report personnel emp (wherevar='deptid=504 and
      mgr=''Jones'' and hiredate>=''01/01/95''')
If your operating system requires it, enclose the entire parameter within double quotes:
report personnel emp "(wherevar='deptid=504 and
      mgr = ''Jones'' and hiredate >= ''01/01/95''')"
Passing Delimited Identifiers
Suppose the value in the valuestring is a delimited identifier. For example, you want a list of employees with some other information about each employee that is to be determined at runtime by entering a column name.
To accomplish this, your report code looks like this:
.declare info = varchar (20)
.query select firstname, lastname, $info as otherinfo
      from emp
The information you want to select from the database is in a column whose name is a delimited identifier.
To specify the delimited identifier as a value on the command line, you must:
1. Specify the column name as a delimited identifier in editable format. For more information, see Delimited Identifiers.
"phone #"
2. Enclose the delimited identifier and its surrounding quotes within single quotes to identify it to Report‑Writer as a string value in the variable=value clause:
info='"phone #"'
3. Enclose the variable=value clause in parentheses. If the parameter contains any characters treated specially by your operating system (such as parentheses in Windows NT), enclose the entire parameter within double quotes and escape any embedded double quotes to pass them through the operating system.
Windows:
Escape the double quotes surrounding a delimited identifier by preceding them with a backslash (\):
report personnel emp "(info='\"phone #\"')"
UNIX:
Escape the double quotes surrounding a delimited identifier by preceding them with a backslash (\):
report personnel emp "(info='\"phone #\"')"
VMS:
Escape the double quotes surrounding a delimited identifier by preceding each with another double quote:
report personnel emp "(info='""phone #""')"
For more information on how to pass delimited identifiers on the command line, see the System Administrator Guide.
Passing String Values with Embedded Quotes
Suppose the database value for which you are entering a comparison value contains embedded single or double quotes, as for example:
"Big John's" Barbecue
Suppose the where clause is specified in the report as:
where $wherevar
You want to specify a value for the variable so that Report‑Writer produces the following SQL query:
where clientname='"Big John''s" Barbecue'
Follow this procedure:
1. Determine how the value appears in the database:
"Big John's" Barbecue
2. Enclose the string value in quotes that are appropriate for your query language (single quotes in SQL). Dereference any embedded quotes (including apostrophes) according to the rules of your query language (in SQL, precede a single quote or apostrophe with another single quote):
'"Big John''s" Barbecue'
3. Enclose the entire valuestring within single quotes to identify it to Report‑Writer as a string. Dereference any embedded single quotes within this string by preceding each single quote with another single quote. This includes any single quotes (or apostrophes) from the original value, as well as those required by your query language in the previous step.
'clientname=''"Big John''''s" Barbecue'''
4. Enclose the entire parameter within parentheses. If the parameter contains any characters treated specially by your operating system (such as parentheses in Windows NT and UNIX or slashes in VMS), enclose the entire parameter within double quotes and escape any embedded double quotes to pass them through the operating system.
Windows:
report accounting receivables "(wherevar=
'clientname=''\"Big John''''s\" Barbecue"''')"
UNIX:
report accounting receivables "(wherevar=
'clientname=''\"Big John''''s\" Barbecue"''')"
VMS:
report accounting receivables "(wherevar=
'clientname=''""Big John''''s"" Barbecue"''')"
Note for QUEL Users
To specify a value for the variable so that Report‑Writer produces the following QUEL query:
where clientname="\"Big John's\" Barbeque"
Follow this procedure:
1. Determine how the value appears in the database:
"Big John's" Barbeque
2. Enclose the string value in quotes that are appropriate for your query language (double quotes in QUEL). Dereference any embedded quotes according to the rules of your query language (in QUEL, precede a double quote with a backslash):
"\"Big John's\" Barbeque"
3. Enclose the entire valuestring within single quotes to identify it to Report‑Writer as a string. Dereference any embedded single quotes (or apostrophes) within this string by preceding each one with a single quote.
'clientname="\"Big John''s\" Barbeque"'
4. Enclose the entire parameter within parentheses. If the parameter contains any characters treated specially by your operating system (such as parentheses in Windows NT and UNIX or slashes in VMS), enclose the entire parameter within double quotes and escape any embedded double quotes to pass them through the operating system.
Windows:
report accounting receivables "(wherevar= 'clientname=\"\\\"Big John''s\\\"Barbeque\"')"
UNIX:
report accounting receivables "(wherevar= 'clientname=\"\\\"Big John''s\\\"Barbeque\"')"
VMS:
report accounting receivables "(wherevar= 'clientname=""\""Big John''s\""Barbeque""')"
Prompted Runtime Variables as Parameters
You can use variables in your report specification that prompt the user for a value at runtime. For string or date values, the report specification must be coded to include quotes around the variable that are appropriate for the query language. If these quotes are included in the report specification, the user can enter the value, as is, without the surrounding quotes. For example, suppose the report specification contains either the following statements:
.declare clientname with prompt 'Enter client's name:'
.query select balance from receivables
      where name = '$clientname'
Or, these QUEL statements:
.declare clientname with prompt 'Enter client's name:'
.query
range of e is receivables
retrieve (e.balance)
      where e.name = "$clientname"
At runtime, the user can enter:
report accounting receivables
Report‑Writer displays the prompt:
Enter client's name:
The user can respond:
Enter client's name: Lakeside Inn
If the report specification omits the query language‑specific quotes around a string or date variable, the user must enter these quotes along with the value on the command line. For example, suppose the report specification contains the following where clause for an SQL or QUEL query:
where name = $clientname
The user must respond to the prompt:
Enter client's name: 'Lakeside Inn'
If the value is a delimited identifier or contains embedded quotes, the user must follow their query language's rules for dereferencing quotes within the string.
Last modified date: 08/28/2024