By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,225 Members | 1,051 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,225 IT Pros & Developers. It's quick & easy.

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

P: n/a
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

Nov 12 '05 #1
Share this Question
Share on Google+
5 Replies


P: n/a

"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
Nov 12 '05 #2

P: n/a
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
Nov 12 '05 #3

P: n/a
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.
Nov 12 '05 #4

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

Markus

Nov 12 '05 #5

P: n/a
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
Nov 12 '05 #6

This discussion thread is closed

Replies have been disabled for this discussion.