Is a statement like this possible: SELECT * FROM (CALL myProcedure)

I created a Stored Procedure like this:
CREATE PROCEDURE myProcedure(IN a_tabschema VARCHAR(20))
DYNAMIC RESULT SETS 1
LANGUAGE SQL
BEGIN
DECLARE rs CURSOR WITH RETURN FOR
SELECT * FROM syscat.tables WHERE tabschema = a_tabschema;
OPEN rs;
END

If I make an CALL myProcedure('SYSIBM') I get an ResultSet

But can I also user the Result in a new Statement like
SELECT * FROM (CALL myProcedure('SYSIBM'));
?

On Question more: Is it possible to set the LIKE-operator
case-insensitive?

Greetings

Markus

re: Is a statement like this possible: SELECT * FROM (CALL myProcedure)

"Markus" <TheRealHawk@Freenet.de> wrote in message
news:1129207983.038094.317480@g44g2000cwa.googlegr oups.com...[color=blue]
> I created a Stored Procedure like this:
> CREATE PROCEDURE myProcedure(IN a_tabschema VARCHAR(20))
> DYNAMIC RESULT SETS 1
> LANGUAGE SQL
> BEGIN
> DECLARE rs CURSOR WITH RETURN FOR
> SELECT * FROM syscat.tables WHERE tabschema = a_tabschema;
> OPEN rs;
> END
>
> If I make an CALL myProcedure('SYSIBM') I get an ResultSet
>
> But can I also user the Result in a new Statement like
> SELECT * FROM (CALL myProcedure('SYSIBM'));
> ?[/color]

No. To do this, what you really want to do is define a table function, and
use that in your SELECT.

For example (slightly abbreviated):

CREATE FUNCTION myFunction(a_tabschema VARCHAR(20))
LANGUAGE SQL
BEGIN
RETURN SELECT * FROM syscat.tables WHERE tabschema = a_tabschema;
END

Then in your application you can do this:

SELECT * FROM TABLE ( myFunction('SYSIBM') ) AS X;
[color=blue]
> On Question more: Is it possible to set the LIKE-operator
> case-insensitive?[/color]

No.

--
Matt Emmerton

re: Is a statement like this possible: SELECT * FROM (CALL myProcedure)

Markus wrote:[color=blue]
> I created a Stored Procedure like this:
> CREATE PROCEDURE myProcedure(IN a_tabschema VARCHAR(20))
> DYNAMIC RESULT SETS 1
> LANGUAGE SQL
> BEGIN
> DECLARE rs CURSOR WITH RETURN FOR
> SELECT * FROM syscat.tables WHERE tabschema = a_tabschema;
> OPEN rs;
> END
>
> If I make an CALL myProcedure('SYSIBM') I get an ResultSet
>
> But can I also user the Result in a new Statement like
> SELECT * FROM (CALL myProcedure('SYSIBM'));
> ?[/color]
Use an SQL Table function.

CREATE FUNCTION myFunction(a_tabschema VARCHAR(20))
RETURNS TABLE (TABSCHEMA VARCHAR(128), .....)
RETURN SELECT * FROM syscat.tables WHERE tabschema = a_tabschema;

SELECT * FROM TABLE(myFunction('Hello')) AS X
[color=blue]
> On Question more: Is it possible to set the LIKE-operator
> case-insensitive?[/color]
No.

Cheers
Serge

--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

re: Is a statement like this possible: SELECT * FROM (CALL myProcedure)

Markus wrote:[color=blue]
> I created a Stored Procedure like this:
> CREATE PROCEDURE myProcedure(IN a_tabschema VARCHAR(20))
> DYNAMIC RESULT SETS 1
> LANGUAGE SQL
> BEGIN
> DECLARE rs CURSOR WITH RETURN FOR
> SELECT * FROM syscat.tables WHERE tabschema = a_tabschema;
> OPEN rs;
> END
>
> If I make an CALL myProcedure('SYSIBM') I get an ResultSet
>
> But can I also user the Result in a new Statement like
> SELECT * FROM (CALL myProcedure('SYSIBM'));
> ?
>
> On Question more: Is it possible to set the LIKE-operator
> case-insensitive?
>
> Greetings
>
> Markus
>[/color]
While the short answer to the second question is no, the sense can be
achieved by something like:

LOWER(attribute) LIKE LOWER(<query-string-containing-%-and/or-_>)

NB This can lead to extensive/expensive tablescans if it is the only
WHERE phrase; in that case CREATE a table/index with a second copy of
attribute with the lower already performed and CREATE an index on that.

re: Is a statement like this possible: SELECT * FROM (CALL myProcedure)

Thanks at all for your answers - i'll try this at work. :-)

Markus

re: Is a statement like this possible: SELECT * FROM (CALL myProcedure)

Bob Stearns wrote:
[color=blue]
> While the short answer to the second question is no, the sense can be
> achieved by something like:
>
> LOWER(attribute) LIKE LOWER(<query-string-containing-%-and/or-_>)
>
> NB This can lead to extensive/expensive tablescans if it is the only
> WHERE phrase; in that case CREATE a table/index with a second copy of
> attribute with the lower already performed and CREATE an index on that.[/color]

And if you make that a GENERATED ALWAYS ... column, he optimizer can
automatically re-route to the additional column.

--
Knut Stolze
DB2 Information Integration Development
IBM Germany