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

[Markus]

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

[Matt Emmerton]

"Markus" <Th*********@Freenet.de> wrote in message
news:11**********************@g44g2000cwa.googlegr oups.com...

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'));
?
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;
On Question more: Is it possible to set the LIKE-operator
case-insensitive?

No.

--
Matt Emmerton

[Serge Rielau]

Markus wrote:

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'));
? 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
On Question more: Is it possible to set the LIKE-operator
case-insensitive?

No.

Cheers
Serge

--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

[Bob Stearns]

Markus wrote:

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

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.

[Markus]

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

Markus

[Knut Stolze]

Bob Stearns wrote:

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.

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