helpinghand
search
needassistance
 
How To
Using procedures to number rows
Category: SQL
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 the following result set on a Mimer SQL 9.2 Engine server. 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 SQL Reference part of the Mimer SQL Documentation Set (html-navigation or PDF-file).

Last updated: 2004-04-30

 

Powered by Mimer SQL

Powered by Mimer SQL