473,327 Members | 2,094 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,327 software developers and data experts.

RETURNS TABLE in DB2

Hi,

I have a following issue in DB2 UDB v8.2. How can I build some data and
store it in a temp table and return as a TABLE to a function.. This
function will be called in a SELECT stmt. something like
SELECT .... FROM TABLE(GetRecursiveCDOs()) AS RecursiveCDOs()

Here is the code: The logic is as follows.When I tried to compile the
code, it errors out at SQLCODE. Is SQLCODE permissable in this context
??

CREATE FUNCTION GetRecursiveCDOs()
RETURNS TABLE(CDODefId INTEGER)
BEGIN ATOMIC
--
DECLARE SQLCODE INTEGER;
DECLARE v_SQLStmt VARCHAR(512);
DECLARE n_CDODefId INTEGER;
DECLARE CDODefs_Cur CURSOR WITH HOLD FOR CDODefsSQL;
DECLARE GLOBAL TEMPORARY TABLE gt_CDODefs(CDODefId INTEGER NOT
NULL) ON COMMIT DELETE ROWS WITH REPLACE NOT LOGGED;
--
SET SQLCODE = 0;
--
SET v_SQLStmt = 'WITH RecursiveCDOs (CDODefId) AS'||
'( SELECT Parent.CDODefId'||
' FROM CDODefinition Parent'||
' WHERE Parent.CDODefId IN ( 1020, 1050 )'||
' UNION ALL'||
' SELECT Child.CDODefId'||
' FROM RecursiveCDOs Parent'||
' ,CDODefinition Child'||
' WHERE Child.ParentCDOId = Parent.CDODefId'||
') SELECT CDODefId FROM RecursiveCDOs';
--
DELETE FROM SESSION.gt_CDODefs;
COMMIT;
--
PREPARE CDODefsSQL FROM v_SQLStmt;
OPEN CDODefs_Cur;
FETCH FROM CDODefs_Cur INTO n_CDODefId;
--
WHILE (SQLCODE = 0) DO
--
INSERT INTO SESSION.gt_CDODefs (CDODefId)
VALUES (n_CDODefId);
--
END WHILE;
--
CLOSE CDODefs_Cur;
--
RETURN SELECT CDODefId
FROM SESSION.gt_CDODefs;
END;

I have tried using a view with just the WITH statement. But our
application will drop the table inside of the view and recreates the
table. The view becomes inoperable because the table was dropped. At
present there is no mechanism to recompile the view. If I create a
function RETURNING TABLE, the table cannot be dropped and the
application hangs. As a work around, the SELECT statement is passed in
as literal to be opened as a cursor and then ith is stored in a GTT and
then query the GTT thus RETURNING the TABLE.

Is there a better way to approach this solution ??

Any ideas will be appreciated...

Thanks,
Purush

Dec 8 '05 #1
7 13776
pu********@gmail.com wrote:
Hi,

I have a following issue in DB2 UDB v8.2. How can I build some data and
store it in a temp table and return as a TABLE to a function.. This
function will be called in a SELECT stmt. something like
SELECT .... FROM TABLE(GetRecursiveCDOs()) AS RecursiveCDOs()

Here is the code: The logic is as follows.When I tried to compile the
code, it errors out at SQLCODE. Is SQLCODE permissable in this context
??

CREATE FUNCTION GetRecursiveCDOs()
RETURNS TABLE(CDODefId INTEGER)
BEGIN ATOMIC
--
DECLARE SQLCODE INTEGER;
DECLARE v_SQLStmt VARCHAR(512);
DECLARE n_CDODefId INTEGER;
DECLARE CDODefs_Cur CURSOR WITH HOLD FOR CDODefsSQL;
DECLARE GLOBAL TEMPORARY TABLE gt_CDODefs(CDODefId INTEGER NOT
NULL) ON COMMIT DELETE ROWS WITH REPLACE NOT LOGGED;
--
SET SQLCODE = 0;
--
SET v_SQLStmt = 'WITH RecursiveCDOs (CDODefId) AS'||
'( SELECT Parent.CDODefId'||
' FROM CDODefinition Parent'||
' WHERE Parent.CDODefId IN ( 1020, 1050 )'||
' UNION ALL'||
' SELECT Child.CDODefId'||
' FROM RecursiveCDOs Parent'||
' ,CDODefinition Child'||
' WHERE Child.ParentCDOId = Parent.CDODefId'||
') SELECT CDODefId FROM RecursiveCDOs';
--
DELETE FROM SESSION.gt_CDODefs;
COMMIT;
--
PREPARE CDODefsSQL FROM v_SQLStmt;
OPEN CDODefs_Cur;
FETCH FROM CDODefs_Cur INTO n_CDODefId;
--
WHILE (SQLCODE = 0) DO
--
INSERT INTO SESSION.gt_CDODefs (CDODefId)
VALUES (n_CDODefId);
--
END WHILE;
--
CLOSE CDODefs_Cur;
--
RETURN SELECT CDODefId
FROM SESSION.gt_CDODefs;
END;

I have tried using a view with just the WITH statement. But our
application will drop the table inside of the view and recreates the
table. The view becomes inoperable because the table was dropped. At
present there is no mechanism to recompile the view. If I create a
function RETURNING TABLE, the table cannot be dropped and the
application hangs. As a work around, the SELECT statement is passed in
as literal to be opened as a cursor and then ith is stored in a GTT and
then query the GTT thus RETURNING the TABLE.

Like swimming in quick sand.. Everytime you move you go deeper...
What about starting at the other end:
Why does your application drop and recreate tables?

SQL Functions in DB2 UDB are essentially macros.
To do anything complex (like dynamic SQL and DDL!) with them you must
use the CALL statement.
Having said that this works great for SQL Scalar functions, but for
table functions you have to problem of linking the result set of teh
proc with the result of the table function. No joy.

So what you would have to do is use an external table function (C, JAVA,
CLR)

Anyway.. fix you orginal problem. Applications don't do DDL!
DBA's do DDL.
Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab
Dec 8 '05 #2
"Serge Rielau" <sr*****@ca.ibm.com> wrote in message
news:3v*************@individual.net...
Anyway.. fix you orginal problem. Applications don't do DDL!
DBA's do DDL.
Cheers
Serge
--
Serge Rielau


Some applications create tables, such a separate audit table for each day.
Dec 8 '05 #3
Serge,

Thanks for the reply.

Here is the reason why we need to drop and recreate tables in DB2?
This application is a metadata repository and allows users to update
that metadata. User might create new columns or drop a metadata table.
The changes in the metadata needs to be reflected into a main database
which will contain other tables too. To do this, we need to create,
drop and populate the data. Users does not need to be connected to the
main database. Just a small footprint of the main database is necessary
on the user machine and user can do changes any time and when ready,
apply the changes to the main DB. There might more than one way to
approach this solution. But, at present, we need to resolve the current
issue and think later about changing the way the application works.

I was confident that DB2 had some thing in the store for this issue. It
tried a similar logic in Oracle and SQL Server. It works like a charm.
Now, just for DB2, I need to think of a External Table function for
DB2. I was hoping to have a FUNCTION based solution for the above issue
for all the 3 DB's. Now, I need to do some coding in Java/C/.NET to
make it work in DB2!!! more maintainance of the application!!!

Thanks,
Purush

Thanks,
Purush

Dec 8 '05 #4
pu********@gmail.com wrote:
Serge,

Thanks for the reply.

Here is the reason why we need to drop and recreate tables in DB2?
This application is a metadata repository and allows users to update
that metadata. User might create new columns or drop a metadata table.
The changes in the metadata needs to be reflected into a main database
which will contain other tables too. To do this, we need to create,
drop and populate the data. Users does not need to be connected to the
main database. Just a small footprint of the main database is necessary
on the user machine and user can do changes any time and when ready,
apply the changes to the main DB. There might more than one way to
approach this solution. But, at present, we need to resolve the current
issue and think later about changing the way the application works.

I was confident that DB2 had some thing in the store for this issue. It
tried a similar logic in Oracle and SQL Server. It works like a charm.
Now, just for DB2, I need to think of a External Table function for
DB2. I was hoping to have a FUNCTION based solution for the above issue
for all the 3 DB's. Now, I need to do some coding in Java/C/.NET to
make it work in DB2!!! more maintainance of the application!!!

What about recreating the view. Note that the view text will still be in
the catalog, it's just marked as invalid.

Cheers
Serge

--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab
Dec 8 '05 #5
Why didn't you use a simple RETURN statement for the Function Body?
Like this:
CREATE FUNCTION GetRecursiveCDOs()
RETURNS TABLE(CDODefId INTEGER)
READS SQL DATA
RETURN
WITH RecursiveCDOs (CDODefId) AS (
SELECT Parent.CDODefId
FROM CDODefinition Parent
WHERE Parent.CDODefId IN ( 1020, 1050 )
UNION ALL
SELECT Child.CDODefId
FROM RecursiveCDOs Parent
, CDODefinition Child
WHERE Child.ParentCDOId = Parent.CDODefId
)
SELECT CDODefId FROM RecursiveCDOs'
;

Another point:
When you use the function, you should write a column name for
correlation name or remove "()" from correlation name.
SELECT CDODefChain
FROM TABLE(GetRecursiveCDOs()) AS RecursiveCDOs(CDODefChain);
or
SELECT CDODefId
FROM TABLE(GetRecursiveCDOs()) AS RecursiveCDOs;

Dec 10 '05 #6
I made mistake.
I should remove a quotation from last SELECT of the function

CREATE FUNCTION ...
....
....
SELECT CDODefId FROM RecursiveCDOs
;

Dec 10 '05 #7
You might want to get a copy of TREES & HIERARCHIES IN SQL. For
example, the nested sets model does not require procedural code at all
to model a tree structure.

Dec 12 '05 #8

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
by: Sreeneet | last post by:
HI all, In SQL Server, i have a function which will return a table. like create function fn_test (@t int) returns table as return (select * from table) now i want the function to retun the...
6
by: BlueDragon | last post by:
Grrr! I'm trying to run a script: print 'Declaring cursor' declare cInv cursor forward_only static for select distinct inv.company, inv.contact, inv.address1, inv.city,
4
by: ezra epstein | last post by:
Aother head banger for me. Below is a complete example of the code Using Postgres 7.4, the function "test" gets this: psql:temp3.sql:10: ERROR: syntax error at or near "%" at character 135...
1
by: I am Sam | last post by:
In ASP using VBScript to replace Chr(13) or Chr(10) with a <br> tag I would use the Replace method so that I can catch carriage returns from a memo field in MS Access or a Text field in MS SQL...
3
by: baka | last post by:
Dear Sir/madam Here i am having some one sql which returns more rows than the required rows after comaparing tables simple sql statement will be lokk like SELECT t.empcd, s.daicd, s.chucd,...
12
by: robertino | last post by:
Hi all, I've put together a few SPs to produce a BOM (bill of materials) listing, which together use a couple of global temp tables, and return the results from a cursor. Here's the code: ...
9
by: serge | last post by:
/* Subject: How to build a procedure that returns different numbers of columns as a result based on a parameter. You can copy/paste this whole post in SQL Query Analyzer or Management Studio...
0
by: h2reyes | last post by:
I have the following query: select sq.*, p.numero, p.nombre from paf p right outer join dbo.GetListOfSquaresForShippingLot(@lot) sq on sq.number = p.numero and sq.version = p.numero The @lot...
2
by: Massimiliano Campagnoli | last post by:
I would like to write an UDF function that returns table A or table B based on a value passed as argument. Something like: CREATE FUNCTION my_func(my_value INTEGER) .... RETURNS TABLE (C1...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.