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.
