SQL output, do I need recursive SQL todo this?

[Lee Dilworth]

Simple example how can I produce a list of tables and their indexes in this
format:

Col1 Col2

Tab1 Index1forTab1
Index2forTab1
Index3forTab1

Tab2 Index1forTab2

Tab3 Index1forTab3
Index2forTab3

you get the idea, I can obviously just do a normal select from
syscat.indexes and do a group by tabname but I wanted to know if it was
possible to neaten the output so that the tabname comes out only once. I
figured I would need some recursion todo this so have been looking for some
examples.

cheers.

[Knut Stolze]

Lee Dilworth wrote:

Simple example how can I produce a list of tables and their indexes in
this format:

Col1 Col2

Tab1 Index1forTab1
Index2forTab1
Index3forTab1

Tab2 Index1forTab2

Tab3 Index1forTab3
Index2forTab3

you get the idea, I can obviously just do a normal select from
syscat.indexes and do a group by tabname but I wanted to know if it was
possible to neaten the output so that the tabname comes out only once. I
figured I would need some recursion todo this so have been looking for
some examples.

You don't need recursion. Just number the rows in each group and then
return the table name only for those rows where the row number equals 1.
Something like this:

SELECT CASE
WHEN rn = 1 THEN table_name
ELSE NULL
END,
index_name
FROM ( SELECT tabname, idxname, row_number() over(partition by tabname)
FROM syscat.indexes ) AS t(table_name, index_name, rn)

p.s: I'm not 100% sure on the exact syntax of the row_number function,
though.

--
Knut Stolze
Information Integration
IBM Germany / University of Jena

[Lee Dilworth]

thanks !!

[Rhino]

"Knut Stolze" <st****@de.ibm.com> wrote in message
news:cs**********@fsuj29.rz.uni-jena.de...

Lee Dilworth wrote:

Simple example how can I produce a list of tables and their indexes in
this format:

Col1 Col2

Tab1 Index1forTab1
Index2forTab1
Index3forTab1

Tab2 Index1forTab2

Tab3 Index1forTab3
Index2forTab3

you get the idea, I can obviously just do a normal select from
syscat.indexes and do a group by tabname but I wanted to know if it was
possible to neaten the output so that the tabname comes out only once. I
figured I would need some recursion todo this so have been looking for
some examples.

You don't need recursion. Just number the rows in each group and then
return the table name only for those rows where the row number equals 1.
Something like this:

SELECT CASE
WHEN rn = 1 THEN table_name
ELSE NULL
END,
index_name
FROM ( SELECT tabname, idxname, row_number() over(partition by tabname)
FROM syscat.indexes ) AS t(table_name, index_name, rn)

p.s: I'm not 100% sure on the exact syntax of the row_number function,
though.

Another possible solution to this problem is to use QMF. QMF has always had
the ability to prevent the duplication of a column (or columns) value from
row to row. I just looked at the QMF V8 Info Center to refresh my memory and
found it right away: on FORM.MAIN you say OUTLINE? ==> YES; QMF takes care
of the rest. This is a very basic and useful function that has been in QMF
from Day One. (You can also control the appearance further with
FORM.OPTIONS.)

Of course this doesn't help much if you don't have QMF ;-)

Rhino