Reference

Net.Data Table Processing Variables

Net.Data defines these variables for use in the REPORT and ROW blocks, unless noted otherwise. Use these variables to reference values that your queries return.

• Nn
• NLIST
• NUM_COLUMNS
• NUM_ROWS
• ROW_NUM
• TOTAL_ROWS
• V_columnName
• VLIST
• Vn

Nn

Purpose

The column name returned by a function call or query for column n.

You can reference Nn in REPORT and ROW blocks.

Examples

Example 1: A variable reference for a column name

The name of column 2 is $(N2).

Example 2: Saves the value of a column name for use outside a REPORT block using DTW_ASSIGN

%define col1=""
...
%function (DTW_SQL) myfunc() {
  select * from atable
  %report {
     @dtw_assign(col1, N1)
     %row{ %}
  %}
%}
 
%html(report) {
@myfunc()
The column name for the first column is $(col1)
%}

Example 3: Nn within an HTML table to define column names

%REPORT{
<H2>Product directory</H2>
<TABLE BORDER=1 CELLPADDING=3>
<TR><TD>$(N1)</TD><TD>$(N2)</TD><TD>$(N3)</TD>
%ROW{
<TR><TD>$(V1)</TD><TD>$(V2)</TD><TD>$(V3)</TD>
%}
</TABLE>
 
%}

NLIST

Purpose

Contains a list of all the column names from the result of a function call or query. The default separator is a space.

You can reference NLIST in REPORT and ROW blocks.

Examples

Example 1: A list of column names with ALIGN

%DEFINE ALIGN="YES"
...
%FUNCTION (DTW_SQL) myfunc() {
select * from MyTable
%report{
Your query was on these columns: $(NLIST).
%row {
...
%}
%}
%}

The list of column names uses a space between column names with ALIGN set to YES.

Example 2: A %LIST variable to change the separator to " | "

%DEFINE %LIST " | " NLIST
...
%FUNCTION (DTW_SQL) myfunc() {
select * from MyTable
%report{
Your query was on these columns: $(NLIST).
%row {
...
%}
%}
%} 

NUM_COLUMNS

Purpose

The number of table columns that Net.Data is processing in the report block; the columns are returned by a function call or query.

You can reference NUM_COLUMNS in REPORT and ROW blocks.

Examples

Example 1: NUM_COLUMNS used as a variable reference with NLIST

%REPORT{
Your query result has $(NUM_COLUMNS) columns: $(NLIST).
...
%}

NUM_ROWS

Purpose

The number of rows in the table that Net.Data is processing in the REPORT block. The number of rows is affected by the value of the upper limit parameter defined for the Net.Data table holding the data. For example, if upper limit is set to 30, but the SELECT statement returns 1000 rows, the value of NUM_ROWS is 30. Additionally, if upper limit is set to 30 and the SELECT statement returns 20 rows, NUM_ROWS equals 20. See TABLE Statement for more information about the TABLE statement and the upper limit parameter.

NUM_ROWS is not affected by the value of START_ROW_NUM as long as START_ROW_NUM is not passed to the language environment. For example, if START_ROW_NUM is set to 5 (specifying that the table displayed on the Web page should be populated starting with row 5) and the SELECT statement returns 25 rows, NUM_ROWS is set to 25, not 21. The first four rows are discarded from the table, but are included in the value of NUM_ROWS. However, if START_ROW_NUM is passed to the language environment, then NUM_ROWS will only contain the number of rows starting at the row specified by START_ROW_NUM. In the example above, NUM_ROWS will be set to 21.

You can reference NUM_ROWS in REPORT and ROW blocks.

Examples

Example 1: Displays the number of names being processed in the REPORT block

%DEFINE DTW_SET_TOTAL_ROWS="YES"
 
%REPORT{
<H2>E-mail directory</H2>
<UL>
%ROW{
<LI>Name: <a href="mailto:$(V1)">$(V2)</a><BR>
Location: $(V3)
%}
</UL>
Names displayed: $(NUM_ROWS)<BR>
Names found: $(TOTAL_ROWS)
%}

ROW_NUM

Purpose

A table variable whose value Net.Data increments each time a row is processed in a Net.Data table. The variable acts as a counter and its value is the number of the current row being processed.

RPT_MAX_ROWS can affect the value of ROW_NUM. For example, if 100 rows are in a table, and you have set RPT_MAX_ROWS to 20, the final value of ROW_NUM is 20, because row 20 was the last row processed.

You can reference ROW_NUM only from within a ROW block.

Examples

Example 1: Populates a column in the HTML output by using ROW_NUM to label each row in the table

%REPORT{
<TABLE BORDER=1>
<TR><TD> Row Number </TD> <TD> Customer </TD>
%ROW{
<TR><TD> $(ROW_NUM) </TD> <TD> $(V_custname) </TD>
%}
</TABLE>
%}

The REPORT block produces a table like the one shown below.

TOTAL_ROWS

Purpose

The total number of rows a query returns, no matter what the value of upper_limit for the TABLE language construct. For example, if RPT_MAX_ROWS is set to display a maximum of 20 rows, but the query returns 100 rows, this variable is set to 100 after ROW processing.

Operating system differences:

• On the OS/400 operating system, this variable can be referenced anywhere in a REPORT or ROW block.
• On the OS/390, OS/2, Windows NT, and UNIX operating systems, this variable can be referenced in the REPORT footer, only.

Language Environment Restriction: Use this variable only with the following database language environments:

• SQL
• ODBC
• Oracle
• Sybase

Required: You must set DTW_SET_TOTAL_ROWS to YES to use this variable. See DTW_SET_TOTAL_ROWS for more information.

Examples

Example 1: Displays the total number of names found

%DEFINE DTW_SET_TOTAL_ROWS="YES"
 
%REPORT{
<H2>E-mail directory</H2>
<UL>
%ROW{
<LI>Name: <a href="mailto:$(V1)">$(V2)</a><BR>
Location: $(V3)
%}
</UL>
Names found: $(TOTAL_ROWS)
%}

V_columnName

Purpose

The value for the specified column name for the current row. The variable is not set for undefined column names. A query containing two column names with the same name gives unpredictable results. Consider using an AS clause in your SQL to rename duplicate column names.

You can reference V_columnName only within a ROW block.

Values

V_columnName

Table 1. V_columnName Values

Examples

Example 1: Using V_columnName as a variable reference

%FUNCTION(DTW_SQL) myQuery() {
 SELECT NAME, ADDRESS from $(qtable)
 %REPORT{
 
 %ROW{
 
 Value of NAME column in row $(ROW_NUM) is $(V_NAME).<BR>
 %}
 %}
 %}

VLIST

Purpose

A list of all the field values for the current row being processed in a ROW block.

You can reference VLIST only within a ROW block. The default separator is a space.

Examples

Example 1: Using list tags to display query results

%DEFINE ALIGN="YES"
 
%REPORT{
Here are the results of your query:
<OL>
%ROW{
<LI>$(VLIST)
%}
</OL>
%}
 

Example 2: Using a list variable to change the separator to <P>

%DEFINE %LIST "<P>" VLIST
 
%REPORT{
Here are the results of your query:
%ROW{
<HR>$(VLIST)
%}
%}

Vn

Purpose

The value for the specified column number n for the current row.

You can reference Vn only within a ROW block.

Net.Data assigns the variable for each field the table; use the variable in a variable reference, specifying the number of the field you want to reference. To use this variable outside the block, assign the value of Vn to a previously defined global variable or an OUT or INOUT function parameter variable.

Examples

Example 1: Report displaying an HTML table

%REPORT{
<H2>E-mail directory</H2>
<TABLE BORDER=1 CELLPADDING=3>
<TR><TD>Name</TD><TD>E-mail address</TD><TD>Location</TD>
%ROW{
<TR><TD>$(V1)</TD>
<TD><a href="mailto:$(V2)">$(V2)</a></TD>
<TD>$(V3)</TD>
%}
</TABLE>
%}

The second column shows the e-mail address. You can send the person a message by clicking on the link.