SQL works... just not in a stored proc

ethansmith · #1 (**permalink**) 11-06-09, 08:36

So I have this table where records have a tie back to their original id/record.

So the table would look like this:

TABLE MAIN

Code:

ID  OrigID  SystemDate
1      0    2009-01-01
2      1    2009-02-07
3      2    2009-02-17
4      0    2009-01-04
5      4    2009-01-06

(So ID 1 and 4 are new/parent records while 2 and 3 and 5 are more or less child records)

Say I have the ID of the most current record in the chain, in this case, ID 3. Now I want to get the original date of the first record in this chain. I can achieve this with the below code:

Code:

WITH PARENT(id,systemdate) AS
    ( 
	SELECT ID,
               systemdate 
	FROM DB.MAIN 
        WHERE ID=3
	UNION ALL
	SELECT C.ORIGID
               C.SYSTEMDATE
	FROM DB.MAIN C,
             PARENT P
	WHERE P.id = C.ID
    )
SELECT systemdate 
FROM PARENT 
ORDER BY id 
fetch first 1 row only;

This code works awesome when ran on it's own. I can run it in Command Editor and success. But, if I create a stored procedure and copy + paste the statement I can't get the stored proc to compile.

Is there something special I need to use or am missing? Amazingly, I can get it to run like this:

Code:

P1: BEGIN
DECLARE cursor1 CURSOR WITH RETURN FOR 
WITH PARENT(id,systemdate) AS
    ( 
	SELECT ID,
               systemdate 
	FROM DB.MAIN 
        WHERE ID=3
	UNION ALL
	SELECT C.ORIGID
               C.SYSTEMDATE
	FROM DB.MAIN C,
             PARENT P
	WHERE P.id = C.ID
    )
SELECT systemdate 
FROM PARENT 
ORDER BY id; 
OPEN cursor1;

But I don't want a result set. Even if I use fetch first 1 row only, it's still a result set. I simply want one variable with the date of, in this example, "2009-01-01".

I'm sure the answer is simple but I just can't get it yet. Maybe there's a better, simpler SQL statement to get the same data.

Any help would be wonderful and greatly appreciated.

Thanks!

--Ethan

DB2 Version: DB2 v9.1.0.3
FixPack: 3
Product Name: DB2 Workgroup Server Edition

ARWinner · #2 (**permalink**) 11-06-09, 10:45

I assume you want to pass back the single value as a OUT parameter.

Look a the SET command: SET OUT_PARM = (select ... )

Andy

ethansmith · #3 (**permalink**) 11-06-09, 11:05

Tried that. It fails when using the WITH AS. I've also tried SELECT systemdate into OUT_PARM from PARENT .... and it fails as well.

I've been researching it this morning and have found, so far, that it looks like DB2 stored procedures want the WITH AS result in a cursor.

So, what I've done is :

(My above call and then)
OPEN cursor1;
FETCH cursor1 INTO OUT_PARM; --Only need the first result
CLOSE cursor1;

This gives me my one parm in (the id) and one parm out (the date).

I just think it can be written a lot better and without the cursor.

Thanks for your response though.

--Ethan