Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Join Date
    Sep 2006
    Posts
    10

    Unanswered: calling procudure in select statement?

    I have a procedure that returns a VARCHAR, there are two INTEGER inputs.

    I need to create a view that has the return values of this procedure.

    I know how to call procedures in java and db2 using call myproc(input1, input2, ?) but is it possible to call a procedure and return that output into a table?

    i.e. if procedure(1, 2, ?) returns 'SOME STRING' then i want a table like:



    --input1-- --input2-- --output--
    --1------ --2------- --SOME STRING--

    essentially i want to make a table that has all the output values for the procedure, I have all the input combinations in a table.


    Any help is greatly appreicated!!


    Thanks

    EDIT: I am using DB2 8.2.1, if what I am asking is impossible please let me know!
    Last edited by Mastakey; 10-19-06 at 17:13.

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    I am fairly certain that what you want to do is not possible. Now if the procedure was a UDF then it is possible.

    Andy

  3. #3
    Join Date
    Sep 2006
    Posts
    10
    Thanks for the info!

    I am trying to call this procedure in a UDF, but the documentation is very hard to follow, at least for me.

    Is there a simple way to call a procedure from a UDF?

  4. #4
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Is the procedure returning a result set or values through out parameters? If it is through a result set, then I think you are out of luck getting at that information.

    Andy

  5. #5
    Join Date
    Sep 2006
    Posts
    10
    Thanks again Andy.

    I am now trying to convert my procedure into a UDF, but I am having one problem. In the procedure I use a cursor, but I am getting errors in my function:

    [IBM][CLI Driver][DB2/6000] SQL0104N An unexpected token "FOR" was found following "0; DECLARE C1 CURSOR". Expected tokens may include: "<SQL_variable_condition_declaration>". LINE NUMBER=15. SQLSTATE=42601

    This is the code:
    Code:
    DECLARE first int default 0;
    DECLARE C1 CURSOR FOR SELECT stringvar FROM table_summary WHERE id=input1 and gid = input2;
    My last question is can you use cursors in functions? if not, is there any equivalent feature that acts the same way?

    I was thinking of doing the function recursively but that will be extremely difficult for me, and it would be much slower performanace wise

    Thanks!

  6. #6
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    No, you cannot use a cursor in a function. Maybe if you post the code for your stored procedure and descrip in detail what you are trying to accomplish, I might be able to help.

    Andy

  7. #7
    Join Date
    Sep 2006
    Posts
    10
    Ok!

    So I have a table called table_summary

    a sample would be:

    select id, gid, stringvar from table_summary where id < 3;

    --id-- --gid-- --stringvar--
    --1-- --1-- --word--
    --1-- --1-- --first--
    --1-- --2-- --univ--
    --1-- --2-- --cup--
    --1-- --2-- --log--
    --1-- --2-- --size--
    --1-- --2-- --end--
    --2-- --2-- --second--
    --2-- --2-- --doover--
    --2-- --3-- --isreal--

    what I want is:

    select id, gid, GETSTRINGFUNC(id, gid) as strings from table summary where id < 3;

    --id-- --gid-- --strings--
    --1-- --1-- --[ word -> first ]--
    --1-- --2-- --[univ -> cup -> log -> size -> end]--
    --2-- --2-- --[ second -> doover]--
    --2-- --3-- --[isreal]--

    my code for the procedure is :

    Code:
    CREATE PROCEDURE GETSTRING ( IN input1 INTEGER, IN input2 INTEGER, OUT outstr varchar(300) )
    RESULT SETS 1
    ------------------------------------------------------------------------
    -- SQL Stored Procedure
    ------------------------------------------------------------------------
    P1: BEGIN
    DECLARE str varchar(300) default ' ';
    DECLARE string varchar(50);
    DECLARE count int;
    DECLARE counter int default 0;
    DECLARE first int default 1;
    DECLARE C1 CURSOR FOR SELECT stringvar FROM table_summary WHERE id=input1 and gid = input2;
    set count= (select count(1) from table_summary WHERE id=input1 and gid = input2);
    
    OPEN C1;
    
    while (counter < count)
    do
    FETCH C1 INTO string;
    if first = 1 THEN
            SET str = '[ ' || string;
            SET first = 0;
            END IF;
    ELSE
            SET str= str||' -> '||string;
            END IF;
    END IF;
    set counter=counter+1;
    end while;
    SET str = str || ' ]';
    set outstr=str;
    END P1
    I am not sure how I can iterate over the tables without using a cursor

  8. #8
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    You are going to have to use recursion. What determines the order of the output column "strings"?

    Andy

  9. #9
    Join Date
    Sep 2006
    Posts
    10
    It has to be in order of the table, i.e. row1 -> row2 -> row3

  10. #10
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    What determines what is row1, row2, etc. In your sample:

    select id, gid, stringvar from table_summary where id < 3;

    --id-- --gid-- --stringvar--
    --1-- --1-- --word--
    --1-- --1-- --first--
    --1-- --2-- --univ--
    --1-- --2-- --cup--
    --1-- --2-- --log--
    --1-- --2-- --size--
    --1-- --2-- --end--
    --2-- --2-- --second--
    --2-- --2-- --doover--
    --2-- --3-- --isreal--

    what I want is:

    select id, gid, GETSTRINGFUNC(id, gid) as strings from table summary where id < 3;

    --id-- --gid-- --strings--
    --1-- --1-- --[ word -> first ]--
    --1-- --2-- --[univ -> cup -> log -> size -> end]--
    --2-- --2-- --[ second -> doover]--
    --2-- --3-- --[isreal]--

    for id = 1 and gid = 2 you have five items. Is there any mechanish to determine the order of those 5 items. What I am trying to get at is that data in a result set in DB2 has no inherent order, so the order of the rows from one query to the next maybe different. If you want the order of "strings" to be the same for every iteration, then I need to know what that is.

    Andy

  11. #11
    Join Date
    Sep 2006
    Posts
    10

    Red face

    Hey!

    The order of the table doesn't really matter for my function as long as it is displayed correctly in the seqence of row1-> row2 etc..

    i.e. if i have

    --1-- --2-- --univ--
    --1-- --2-- --cup--
    --1-- --2-- --log--
    --1-- --2-- --size--
    --1-- --2-- --end--

    then the function should return [univ -> cup -> log -> size -> end]

    and

    --1-- --2-- --log--
    --1-- --2-- --size--
    --1-- --2-- --end--
    --1-- --2-- --univ--
    --1-- --2-- --cup--

    should return [log -> size -> end -> univ -> cup]

    Thanks for helping

    I am trying to do it recursively and it's giving me a headache

  12. #12
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Let me give it a shot.


    with t1 (id, gid, ordinal, stringvar) as
    (select id,gid,row_number() over (partition by id,gid),stringvar from mytable
    ),
    t2 (tid,gid,strings) as
    (select id,gid,'['||stringvar from t1 where ordinal = 1
    Union all
    select p.id,p.gid,p.strings||' -> ' || t.stringvar
    from t1 as t, t2 as p
    where (t.id = p.td and t.gid = p.gid and( t.ordinal = (p.ordinal + 1)))
    ) select id,gid,strings||']' from t2

    Andy

  13. #13
    Join Date
    Sep 2006
    Posts
    10
    Hey, thanks for that code !
    some syntax issues with that, I'm not sure but should t2 have ordinal in it's columns? you use p.ordinal but don't have ordinal in t2.

  14. #14
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    I do not see any syntax issues. There is a typo in the where clause. It should be t.id = p.id. There is no need for ordinal in t2. T1 is basically your table with the rows numbered in the groups of (id,gid) so I can build the strings from there. T2 is building the result you want.

    Andy

  15. #15
    Join Date
    Sep 2006
    Posts
    10
    Hey, I am getting a syntax error

    SQL0206N "P.ORDINAL " is not valid in the context where it is used.

    I added the ordinal in the code, and did a little bit of editing and it works!!!

    Thanks again, you saved me from many hours of suffering

Posting Permissions

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