Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2007
    Posts
    12

    Question Unanswered: SQL "WITH AS" works... just not in a stored proc

    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
    Last edited by ethansmith; 11-06-09 at 10:21. Reason: Better Title

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    I assume you want to pass back the single value as a OUT parameter.

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

    Andy

  3. #3
    Join Date
    Jan 2007
    Posts
    12
    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

  4. #4
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    Quote Originally Posted by ARWinner View Post
    I assume you want to pass back the single value as a OUT parameter.

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

    Andy
    or

    SELECT systemdate into out_parm
    FROM PARENT
    ORDER BY id;
    OPEN cursor1;
    Visit the new-look IDUG Website , register to gain access to the excellent content.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •