Results 1 to 6 of 6
  1. #1
    Join Date
    Feb 2005
    Posts
    3

    Unanswered: Problem with DB2 Procedure (Passing Parameter to Cursors)

    Hi,

    I have problem in passing parametre to cursor in an procedure.

    Below is the procedure, in which I have tried to builde a string of data seperated by comma. When I pass this comma seperated string to cursor it is not returning me any data. At the same time it is not returning me any error as well.

    I tried to print and see whether I am passing the correct data to the cursor. and It looked fine to me.

    The same procedure will work fine if I hard code the values (Like in my procedure v_orderNumberStr = '97637' But the same will not work when I dyanmicaaly build the string and pass it to cursor.

    Can anybody please tell me what is the problem in procedure.



    CREATE PROCEDURE SP_GET_DEALER_SHIP_BUILDER(
    IN custBase VARCHAR(7),
    IN custSuffix VARCHAR(6)
    )
    DYNAMIC RESULT SETS 2
    LANGUAGE SQL
    P1: BEGIN

    DECLARE v_orderNumber INTEGER DEFAULT 0;
    DECLARE v_orderNumberStr VARCHAR(1040);
    DECLARE v_orderNumberStr1 VARCHAR(1040);

    DECLARE curDlrShipBuilder CURSOR FOR
    SELECT CHAR(R1SSNG) FROM LCR1CPP WHERE CHAR(R1SSNG) in (v_orderNumberStr1) ORDER BY 1;

    SET v_orderNumberStr = '97637';

    BEGIN

    FOR v_row AS SELECT R1SSNG FROM LCR1CPP WHERE R1WKCD='3917' AND R1WLCD='0' AND R1MCSV='00' and R1ZVCD='P' and R1ONSV='L'
    DO
    SET v_orderNumberStr = v_orderNumberStr || ', ' || TRIM(CHAR( v_row.R1SSNG)) ;
    END FOR;
    END;

    SET v_orderNumberStr1 = TRIM(CHAR(v_orderNumberStr));


    BEGIN
    --OPEN curDlrShipBuilder1;
    OPEN curDlrShipBuilder;
    END;

    END P1


    Thanks and Regards,
    Babu

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    That's because you are in fact passing to the IN expression a single character value, containing by coincidence something like '97637,97638,97639'. It's the difference between
    Code:
    ... WHERE CHAR(R1SSNG) in ('97637,97638,97639')...,
    which is what you have, and
    Code:
     ... WHERE CHAR(R1SSNG) in (97637,97638,97639)...,
    which is what you want.

    I think you'll be able to achieve your result by using a subselect directly in the IN expression:
    Code:
    ... WHERE R1SSNG in (SELECT R1SSNG FROM LCR1CPP WHERE R1WKCD='3917' AND R1WLCD='0' AND R1MCSV='00' and R1ZVCD='P' and R1ONSV='L')...

  3. #3
    Join Date
    Feb 2005
    Posts
    3

    Problem with DB2 Procedure (Passing Parameter to Cursors)

    Hi,

    Thanks for your reply.

    The basic idea of me joining the data (comma seperated) and sending to cursor is to improve the performance of the Query I have. Basically I have a big query which joins more than 10 tables/files. Some of these tables have huge data (more than million records). So What I did was, I broke the Query in to peices and executed them seperately. This logic Improved the peformnace drastically. So Now, Since I have 3 seperate queries, I wanted put them in procedure, where in the output each has to be merged and sent to the next query. This is the reason, I just tried with a simple query and tried to pass the comma seperated data to it.

    So, I do not want to Join the Query as it is going to cause me performance problems.

    If you know any other solution to my problem, it will be well appricated.

    Thank you for your time
    Babu







    to query is to break up the data and

  4. #4
    Join Date
    Jan 2005
    Posts
    191
    You might care to insert the selected values into a temporary table and then use the temp table for the join. Something along the lines of:

    DECLARE curDlrShipBuilder CURSOR FOR
    SELECT CHAR(R1SSNG) FROM LCR1CPP t1, session.tt t2 WHERE t1.R1SSNG = t2.r1ssng;

    SET v_orderNumberStr = '97637';

    declare global temprary table tt as (SELECT R1SSNG FROM LCR1CPP) definition only;

    insert into session.tt values(97637);

    insert into session.tt SELECT R1SSNG FROM LCR1CPP WHERE R1WKCD='3917' AND R1WLCD='0' AND R1MCSV='00' and R1ZVCD='P' and R1ONSV='L' ;

    --OPEN curDlrShipBuilder1;
    OPEN curDlrShipBuilder;


    James Campbell

  5. #5
    Join Date
    Feb 2005
    Posts
    3

    Problem with DB2 Procedure (Passing Parameter to Cursors)

    Hi James,

    Thankyou for your reply, The logic works, but the if you try to execute the same procedure many times in the same session, it says the table already exists. So I used 'WITH REPLACE' when creating the remporary table.

    With this, I have one more problem. I am passing a comma seperated value to the procedure. Again I have the same problem, it is treating it as a Single string.

    For EX, '0','0001','0002' This is the string I am passing to the procedure and I want to use it in the query with in the IN clause.

    Any Ideas,

    Thanks again for your logic.
    Cheers,
    Babu

  6. #6
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    You'll have to use EXECUTE IMMEDIATE then.

Posting Permissions

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