{eachrow} Directive

Delimits the eachrow section

Syntax

{eachrow <n="n1"> <first="n2"> <last="n3"> <closequery="yes|no">}...{/eachrow}

Optional Arguments

n="n1"

n1 is the total number of rows that you want htmSQL to retrieve. A value of max tells htmSQL to retrieve all the rows in the results set. The default value is max.

The n= and last= parameters are mutually exclusive. When you specify both of them, whichever parameter is specified last is the one that is used. For example, if you specify the following, then the last row that is retrieved is row 10:

{eachrow last="15" n="10"}

first="n2": n2 is the number of the first row that you want htmSQL to retrieve. The default value is 1.

last="n3"

n3 is the number of the last row that you want htmSQL to retrieve. A value of max specifies the last row in the results set. For example, if you specify the following, then you retrieve all but the first 19 rows in the results set:

{eachrow first="20" last="max"}

{eachrow n="10" last="15"}

closequery="yes|no"

closequery="yes" tells htmSQL to send the SAS/SHARE server a message to terminate query processing when htmSQL finishes processing the eachrow section. When the SAS/SHARE server terminates query processing, it closes the input tables and frees the memory associated with this query. If you do not specify this parameter or if you specify closequery="no", then query processing is not terminated until htmSQL finishes processing the main input file.

Specify this parameter

if your htmSQL input file contains more than 64 queries to the same SAS/SHARE server
if you want to perform a DROP or ALTER TABLE on a table that the query refers to.

Note: If you specify this parameter, then no references to the columns of the results set are allowed after the eachrow section.

Details

The {eachrow} and {/eachrow} directive pair delimits the eachrow section. The eachrow section is a part of the query section and contains instructions on how to format the results that are generated by the SQL section that immediately precedes that eachrow section. The formatting information is applied to each row of the results set and can include any valid HTML tag and variable reference. The variable references contained in the eachrow section are resolved for each row of output as that row is formatted.

Note: Because htmSQL sends all HTML information to stdout exactly as it is encountered, eachrow sections that are enclosed within HTML PRE elements might not format the way that you expect them to. If your {eachrow} directive is followed by a line break, htmSQL sends that line break to stdout and causes the output to appear double-spaced. The following lines

<pre>
{eachrow}
X: {&x} Y: {&y}
{/eachrow}
</pre>

are sent to stdout (the Web browser) as

<pre>

X: 1     Y: A

X: 2     Y: B

X: 3     Y: C

</pre>

and the Web browser displays the following double-spaced output on the Web page:

X: 1     Y: A

X: 2     Y: B

X: 3     Y: C

You can avoid double-spacing by putting the {eachrow} directive on the same row as the variables:

<pre>
{eachrow}X: {&x}     Y: {&y}
{/eachrow}
</pre>

If you want to submit more SQL statements from within your eachrow section, you can do one of the following:

nest one or more SQL sections (with accompanying eachrow and norows sections) in the eachrow section. All queries are sent to the data source that is specified by the encompassing {query} directive.

Note: You can also nest a {library} directive. However, it is recommended that you nest them only if your {library} directive contains variable references that change as the eachrow section is processed. Otherwise, if the parameters in the {library} directive have static values, then place the {library} directive before the eachrow section to avoid unnecessary processing.
nest a complete query section in the eachrow section, and use the {query} directive to specify a different data source. The SQL, eachrow, and norows sections that are within the nested section will work with data from this new data source.
nest a complete update section in the eachrow section to update data from the same data source or from a different data source.

Note: htmSQL does not limit the number of times that you can nest query sections or SQL/eachrow section pairs within eachrow sections. However, beyond a certain point, you might experience poor performance or your system might run out of memory.