Results 1 to 10 of 10
  1. #1
    Join Date
    Dec 2009
    Posts
    5

    Question Unanswered: Help required on cursor and procedure

    Hello,

    I am new to DB2 SQL and am struggling trying to create a procedure, basically I need loop through entries in a table which contains rules required for calulcating territories of data, I then need to run a select statement based on the entry in the table and insert a value in a table if this query returns a result, this is the code but I am struggling getting the procedure created, any help would be great I am running out of time.

    Here is the code....

    create procedure Tags.ApplyTAG(in strUNID varchar(32), out strResult varchar(1))
    LANGUAGE SQL
    MODIFIES SQL DATA
    DECLARE cr CURSOR FOR
    Select TAGS.DAVDATATAGRULES.BuiltSQLQuery, TAGS.TAGS.TAGCODE From TAGS.DAVDATATAGRULES inner join tags.tags on TAGS.DAVDATATAGRULES.TAGNAME = tags.TAGS.TAGNAME for read only;
    Open cr;
    Fetch from cr Into :workonBuiltSQLQuery, :workonTAGCode, :workonTagName, :workonTagType;
    IF EXISTS (SELECT #UNID FROM TABLE.ORGS where "#UNID"= strUNID and concat :workonBuiltSQLQuery)
    Insert into TAGS.TAGMAP (ORGUNID, TAGCODE) values (strUNID, :workonTagCode)
    end
    Close cr;
    end

    Thanks
    Fiona

  2. #2
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963

    Thumbs down

    Quote Originally Posted by StewartFJ View Post
    Hello,

    I am new to DB2 SQL and am struggling trying to create a procedure, basically I need loop through entries in a table which contains rules required for calulcating territories of data, I then need to run a select statement based on the entry in the table and insert a value in a table if this query returns a result, this is the code but I am struggling getting the procedure created, any help would be great I am running out of time.

    Here is the code....

    create procedure Tags.ApplyTAG(in strUNID varchar(32), out strResult varchar(1))
    LANGUAGE SQL
    MODIFIES SQL DATA
    DECLARE cr CURSOR FOR
    Select TAGS.DAVDATATAGRULES.BuiltSQLQuery, TAGS.TAGS.TAGCODE From TAGS.DAVDATATAGRULES inner join tags.tags on TAGS.DAVDATATAGRULES.TAGNAME = tags.TAGS.TAGNAME for read only;
    Open cr;
    Fetch from cr Into :workonBuiltSQLQuery, :workonTAGCode, :workonTagName, :workonTagType;
    IF EXISTS (SELECT #UNID FROM TABLE.ORGS where "#UNID"= strUNID and concat :workonBuiltSQLQuery)
    Insert into TAGS.TAGMAP (ORGUNID, TAGCODE) values (strUNID, :workonTagCode)
    end
    Close cr;
    end

    Thanks
    Fiona
    Who can help you ?

    Where is your Declare Section for host variables:
    :workonBuiltSQLQuery, :workonTAGCode, :workonTagName, :workonTagType;
    ???
    How you make loop ? How you exit from loop ?

    Lenny

  3. #3
    Join Date
    Dec 2009
    Posts
    5
    Told you I was new to this

  4. #4
    Join Date
    Dec 2009
    Posts
    5

    So any pointers

    I thought the loop was carried out via the fetch from the cursor?

    The more I search for information the more I get confused

  5. #5
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963

    Question

    Quote Originally Posted by StewartFJ View Post
    Told you I was new to this
    You want to study DB2 on the Forums ?

    Is this new way ?
    Let me know I want to study Japanese. Maybe I can find the forum....

    Lenny

  6. #6
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963

    Lightbulb

    You don't need any cursors.

    Following query will do for you what do you need:

    Code:
    Insert into TAGS.TAGMAP (ORGUNID, TAGCODE) 
    Select t1.BuiltSQLQuery, t2.TAGCODE 
    From TAGS.DAVDATATAGRULES t1 inner join tags.tags  t2
    on t1.TAGNAME = t2.TAGNAME
    That's it ! You have to understand DB2 and all become easy....

    Lenny

  7. #7
    Join Date
    Dec 2009
    Posts
    5

    Wink Thanks for the pointers

    Are you able to see why this would not work, I get error -206 42703

    create procedure Tags.ApplyTAG(in strUNID varchar(32))
    LANGUAGE SQL
    P1: BEGIN

    declare workonBuiltSQLQuery varchar(200);
    declare workonTAGCode integer;
    declare tmpValue varchar(32);

    DECLARE c0 CURSOR FOR
    Select TAGS.DAVDATATAGRULES.BuiltSQLQuery, TAGS.TAGS.TAGCODE From TAGS.DAVDATATAGRULES
    inner join tags.tags on TAGS.DAVDATATAGRULES.TAGNAME = tags.TAGS.TAGNAME for read only;

    declare c1 cursor for v_stmt;

    open c0;

    Fetch from c0 Into workonBuiltSQLQuery, workonTAGCode;
    while sqlcode <> 100 do

    set v_sql ='SELECT "#UNID" FROM TAB.ORGS where '|| workonBuiltSQLQuery;
    prepare v_stmt from v_sql;
    open c2;
    fetch from c2 into tmpValue;
    while sqlcode <> 100 do
    Insert into TAGS.TAGMAP(ORGUNID, TAGCODE) values (tmpValue, workonTagCode);
    end while;
    close c2;
    end while;
    close c0;
    END P1

  8. #8
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    SQL0206N may indicate that one of the database object identifiers (table name or column name) is invalid. Also, you seem to be using a lot of undeclared variables in your code.
    ---
    "It does not work" is not a valid problem statement.

  9. #9
    Join Date
    Dec 2009
    Posts
    5
    Thank you so much I have been able to create the procedure, appreciate the help.

  10. #10
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Quote Originally Posted by Lenny77 View Post
    You have to understand DB2 and all become easy....
    Actually, that's basic/standard SQL.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

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
  •