Introduction
A frequently asked feature is the ability to number rows within a result set. Database products solve this problem in different ways. Up until SQL-2003 there has not been such a feature defined in the standard, but in SQL-2003 it is part of the extension T611 “Elementary OLAP operations” — see the chart Optional Features in SQL-2003. (This optional feature is not yet available in Mimer SQL).
This article presents an example on how to solve this problem using a result set procedure. At the same time, the example below shows how to use the SQL ROW data type.
Description
The solution is to embed the query in a result set procedure, and simply use an integer to count returned rows. The below procedure produces a list of the collations installed into the database server. For simplicity, it returns only two columns, the row number and a combination of the collation schema and its name.
create procedure NUMBER_ROWS_QUERY_1 () values (integer,varchar(258)) as (NR,COLLATION) reads sql data begin declare CURS cursor for select * from INFORMATION_SCHEMA.COLLATIONS; declare ROWNUM integer; declare exit handler for not found close CURS; declare rc ROW as (INFORMATION_SCHEMA.COLLATIONS); set ROWNUM=0; open CURS; loop set ROWNUM=ROWNUM+1; fetch CURS into rc; return (ROWNUM,rc.COLLATION_SCHEMA||'.'||rc.COLLATION_NAME); end loop; close CURS; end
The above procedure returns a result set like the following. Only the first 14 rows are shown.
SQL>call number_rows_query_1(); NR COLLATION =========== ========================================================= 1 INFORMATION_SCHEMA.ISO8BIT 2 INFORMATION_SCHEMA.SQL_TEXT 3 INFORMATION_SCHEMA.UCS_BASIC 4 INFORMATION_SCHEMA.EOR 5 INFORMATION_SCHEMA.EOR_3 6 INFORMATION_SCHEMA.EOR_1 7 INFORMATION_SCHEMA.UNICODE_3 8 INFORMATION_SCHEMA.UNICODE_1 9 INFORMATION_SCHEMA.AFRIKAANS_3 10 INFORMATION_SCHEMA.AFRIKAANS_1 11 INFORMATION_SCHEMA.ALBANIAN_3 12 INFORMATION_SCHEMA.ALBANIAN_1 13 INFORMATION_SCHEMA.ARABIC_3 14 INFORMATION_SCHEMA.ARABIC_1 Continue ?
Links
Read more about the ROW data type and result set procedures in the Reference Manual of the Mimer SQL Documentation set.