Results 1 to 10 of 10
  1. #1
    Join Date
    Feb 2004
    Posts
    7

    Unanswered: DB2 Stored Procedure

    I'm hoping you can help me - I'm new to DB2 and I can't figure out the correct SQL code for the stored procedure I am attempting to write.

    I'm trying to read a SQL select statement to output a list of material numbers associated with a particular offer - eventually I need to insert the material numbers and offer_code into another table. I have attempted to do this logic in many ways and can't figure out how to read the select statment to retrieve the material numbers. The stored procedure below is to the point where I output the select statements. Cursor c2 can return more than one row. How do I execute the select statements returned by c2 to retrieve the material numbers ?

    create procedure test (IN offer_code_in char(6))

    begin

    Declare offer_TEMP int default 2;
    Declare condition_TEMP varchar(2048);

    declare c1 cursor with return for
    select offerid
    from offerinstance
    where offercode = offer_code_in;

    DECLARE c2 CURSOR WITH RETURN FOR
    SELECT 'select materialnum from product where 'CONCAT REPLACE(CONDITION, 'ProductID','MATERIALNUM')
    from offertoproduct
    Where offerid = offer_TEMP order by condition;

    open c1;
    Fetch c1 into offer_TEMP;
    close c1;

    open c2;
    Fetch c2 into condition_TEMP;
    close c2;

    end


    We are using DB2 UDB V8.1 on AIX5 (I'm assuming cause our client requires 64 bit)

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1

    Re: DB2 Stored Procedure

    Originally posted by ShelleyMKG

    create procedure test (IN offer_code_in char(6))

    begin

    Declare offer_TEMP int default 2;
    Declare condition_TEMP varchar(2048);

    declare c1 cursor with return for
    select offerid
    from offerinstance
    where offercode = offer_code_in;

    DECLARE c2 CURSOR WITH RETURN FOR
    SELECT 'select materialnum from product where 'CONCAT REPLACE(CONDITION, 'ProductID','MATERIALNUM')
    from offertoproduct
    Where offerid = offer_TEMP order by condition;

    open c1;
    Fetch c1 into offer_TEMP;
    close c1;

    open c2;
    Fetch c2 into condition_TEMP;
    close c2;

    end


    We are using DB2 UDB V8.1 on AIX5 (I'm assuming cause our client requires 64 bit)
    I think if you move your first FETCH before DECLARE C2 it should work - otherwise at the time your variable (offer_TEMP) is evalued it still contains the default value.

    Alternatively, you could do everything in a single statement:

    select offerid
    from offerinstance
    where offercode = offer_code_in;


    SELECT 'select materialnum from product where 'CONCAT REPLACE(CONDITION, 'ProductID','MATERIALNUM')
    from offertoproduct
    Where offerid =
    (select offerid
    from offerinstance
    where offercode = offer_code_in)
    order by condition;

  3. #3
    Join Date
    Feb 2004
    Posts
    7

    Re: DB2 Stored Procedure

    Thanks - but that's not really my issue - this statement returns the results I'm looking for - select statement(s). But now I need to know how to execute the select statements to return a list of material numbers. And then I need to insert the list of material numbers along with the input variable offer_code into another table.


    Originally posted by n_i
    I think if you move your first FETCH before DECLARE C2 it should work - otherwise at the time your variable (offer_TEMP) is evalued it still contains the default value.

    Alternatively, you could do everything in a single statement:

    select offerid
    from offerinstance
    where offercode = offer_code_in;


    SELECT 'select materialnum from product where 'CONCAT REPLACE(CONDITION, 'ProductID','MATERIALNUM')
    from offertoproduct
    Where offerid =
    (select offerid
    from offerinstance
    where offercode = offer_code_in)
    order by condition;

  4. #4
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1

    Re: DB2 Stored Procedure

    Originally posted by ShelleyMKG
    Thanks - but that's not really my issue - this statement returns the results I'm looking for - select statement(s). But now I need to know how to execute the select statements to return a list of material numbers. And then I need to insert the list of material numbers along with the input variable offer_code into another table.
    I'm not sure I understand the problem correctly.

    You can execute dynamic SQL with EXECUTE IMMEDIATE. Retrieve you 'SELECT MATERIALNUM ...' into a variable (say, temp_sel) and issue EXECUTE IMMEDIATE temp_sel.

    However, the entire setup looks too complex to me. There must be an easeier way to accomplish what you need.

    What is in the CONDITION field?

  5. #5
    Join Date
    Feb 2004
    Posts
    7

    Re: DB2 Stored Procedure

    I'm getting closer...
    create procedure test6 (IN offer_code_in char(6))

    begin

    Declare condition_TEMP varchar(2048);

    declare c1 cursor with return for
    SELECT 'select materialnum from product where 'CONCAT REPLACE(CONDITION, 'ProductID','MATERIALNUM')
    from offertoproduct
    Where offerid =
    (select offerid
    from offerinstance
    where offercode = offer_code_in)
    order by condition;

    Declare c2 cursor with return for
    select materialnum
    from product
    where MATERIALNUM = ANY (condition_TEMP);

    open c1;
    Fetch c1 into condition_TEMP;
    close c1;

    open c2;

    end


    The following statements works outside of the stored proc:

    select materialnum
    from product
    where MATERIALNUM = ANY (select materialnum from product where HIGHERLEVEL = '15' AND LOWERLEVEL = 'R3')

    select materialnum
    from product
    where MATERIALNUM = ANY (select materialnum from product where MATERIALNUM = '000000000037005088')


    But I get the following error when trying to compile:
    Error: SQL0104N An unexpected token "ANY (condition_TEMP) open" was found following " where MATERIALNUM =". Expected tokens may include: "<space>". LINE NUMBER=21. SQLSTATE=42601
    (State:42601, Native Code: FFFFFF98)
    or
    Error: SQL0104N An unexpected token "condition_TEMP" was found following "materialnum = ANY (". Expected tokens may include: "<values>". LINE NUMBER=19. SQLSTATE=42601
    (State:42601, Native Code: FFFFFF98)

    Originally posted by ShelleyMKG
    Thanks - but that's not really my issue - this statement returns the results I'm looking for - select statement(s). But now I need to know how to execute the select statements to return a list of material numbers. And then I need to insert the list of material numbers along with the input variable offer_code into another table.
    Last edited by ShelleyMKG; 02-19-04 at 11:22.

  6. #6
    Join Date
    Feb 2004
    Posts
    7

    Re: DB2 Stored Procedure

    Thanks again for helping...I got a little closer and posted a different reply..but I tried the execute immediate and here are my results:
    create procedure test8 (IN offer_code_in char(6))

    begin

    Declare condition_TEMP varchar(2048);

    declare c1 cursor with return for
    SELECT 'select materialnum from product where 'CONCAT REPLACE(CONDITION, 'ProductID','MATERIALNUM')
    from offertoproduct
    Where offerid =
    (select offerid
    from offerinstance
    where offercode = offer_code_in)
    order by condition;

    open c1;
    Fetch c1 into condition_TEMP;
    close c1;

    execute immediate condition_TEMP;

    end
    SQL0084N An EXECUTE IMMEDIATE statement contains a SELECT or
    VALUES statement.

    Explanation:

    A SELECT or VALUES statement was used in an EXECUTE IMMEDIATE
    statement.

    The statement cannot be processed.

    User Response:

    The implied function is not supported. Prepare the SELECT or
    VALUES statement. Then use OPEN, FETCH, and CLOSE.

    sqlcode : -84

    sqlstate : 42612


    The Condition field can contain "ProductID = '000000000037005088'
    or a grouping of ProductId's - "HIGHERLEVEL = '15' AND LOWERLEVEL = 'R3'". I can retrieve more than one or many conditions per offerid also.

    Originally posted by n_i
    I'm not sure I understand the problem correctly.

    You can execute dynamic SQL with EXECUTE IMMEDIATE. Retrieve you 'SELECT MATERIALNUM ...' into a variable (say, temp_sel) and issue EXECUTE IMMEDIATE temp_sel.

    However, the entire setup looks too complex to me. There must be an easeier way to accomplish what you need.

    What is in the CONDITION field?

  7. #7
    Join Date
    Feb 2004
    Posts
    107

    Re: DB2 Stored Procedure

    Hi Shelley MKG,

    I don't think the select statement used for the cursor c1 will compile.
    I doubt my understanding about your problem. Hence i like to explain my understanding before stating the solution.

    My Understanding:
    Three tables are involved in your problem: OfferInstance(offerId, offerCode), OfferToProduct(offerId, productId), and Product(productId, materialNum).
    Input to the procedure is offerCode. The materialNum's related to the offerCode (through OfferInstance.offerId = OfferToProduct.offerId AND OfferToProduct.productId = Product.productId) have to be inserted into another table.

    Solution:
    -------------------------------------------------------------------
    CREATE PROCEDURE TEST(IN offer_code_in char(6))
    LANGUAGE SQL
    BEGIN
    -- For each row returned by the select statement,
    -- the statement block inside the for loop will be executed
    -- where the offercode and materialNum are inserted into another table 'NEWTABLE'.
    -- Remember to create the table NEWTABLE before executing this procedure.
    FOR vl AS
    SELECT Product.materialNum materialNum
    FROM OfferInstance, OfferToProduct, Product
    WHERE Product.productId = OfferToProduct.productId
    AND OfferToProduct.offerId = OfferInstance.offerId
    AND OfferInstance.offerCode = offer_code_in
    DO
    INSERT INTO NEWTABLE VALUES (offer_code_in, materialNum);
    END FOR;
    END
    @
    -------------------------------------------------------------------
    Note: I haven't tested it. But believing that it will work.

    Please let me know the results.

    With Regards,
    Jake

    Originally posted by ShelleyMKG
    Thanks again for helping...I got a little closer and posted a different reply..but I tried the execute immediate and here are my results:
    create procedure test8 (IN offer_code_in char(6))

    begin

    Declare condition_TEMP varchar(2048);

    declare c1 cursor with return for
    SELECT 'select materialnum from product where 'CONCAT REPLACE(CONDITION, 'ProductID','MATERIALNUM')
    from offertoproduct
    Where offerid =
    (select offerid
    from offerinstance
    where offercode = offer_code_in)
    order by condition;

    open c1;
    Fetch c1 into condition_TEMP;
    close c1;

    execute immediate condition_TEMP;

    end
    SQL0084N An EXECUTE IMMEDIATE statement contains a SELECT or
    VALUES statement.

    Explanation:

    A SELECT or VALUES statement was used in an EXECUTE IMMEDIATE
    statement.

    The statement cannot be processed.

    User Response:

    The implied function is not supported. Prepare the SELECT or
    VALUES statement. Then use OPEN, FETCH, and CLOSE.

    sqlcode : -84

    sqlstate : 42612


    The Condition field can contain "ProductID = '000000000037005088'
    or a grouping of ProductId's - "HIGHERLEVEL = '15' AND LOWERLEVEL = 'R3'". I can retrieve more than one or many conditions per offerid also.

  8. #8
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1

    Re: DB2 Stored Procedure

    Originally posted by ShelleyMKG

    execute immediate condition_TEMP;

    end
    SQL0084N An EXECUTE IMMEDIATE statement contains a SELECT or
    VALUES statement.

    Right.. My bad. I have never used EXECUTE IMMEDIATE myself so it was a wrong guess - EXECUTE IMMEDIATE does not support SELECTs, as it appears.

    I can't think of any other way to accomplish what you want within an SP because there's no way to execute dynamic select statements built "on the fly". You could probably do it using an OS script though, something like:

    db2 +x "select 'db2 \"select materialnum <...the rest of C1 definition...>\" ' | sh

    which will send the SELECT statements generated by your first SELECT to the shell for execution. I'm not sure it's sutable for your situation.

  9. #9
    Join Date
    Feb 2004
    Posts
    7

    Re: DB2 Stored Procedure

    Hi Jake -

    Thanks for the input. I wish my solution was what you stated but the OfferToProduct table does not contain the actual productID's instead it contains a condition (i.e. ProductID = '12345' or GroupOfProductId's = 'abc') I have to extract this condition from the OfferToProduct table and apply it to the Product table in order to pull back the list of ProductId's associated to a particular Offer.

    Once I have a list of ProductId's that belong to an Offer I need to insert the ProductId's and OfferCode into another table that already exists.

    Thanks for the input - I appreciate the help.
    ~ShelleyMKG.
    Originally posted by Jake K
    Hi Shelley MKG,

    I don't think the select statement used for the cursor c1 will compile.
    I doubt my understanding about your problem. Hence i like to explain my understanding before stating the solution.

    My Understanding:
    Three tables are involved in your problem: OfferInstance(offerId, offerCode), OfferToProduct(offerId, productId), and Product(productId, materialNum).
    Input to the procedure is offerCode. The materialNum's related to the offerCode (through OfferInstance.offerId = OfferToProduct.offerId AND OfferToProduct.productId = Product.productId) have to be inserted into another table.

    Solution:
    -------------------------------------------------------------------
    CREATE PROCEDURE TEST(IN offer_code_in char(6))
    LANGUAGE SQL
    BEGIN
    -- For each row returned by the select statement,
    -- the statement block inside the for loop will be executed
    -- where the offercode and materialNum are inserted into another table 'NEWTABLE'.
    -- Remember to create the table NEWTABLE before executing this procedure.
    FOR vl AS
    SELECT Product.materialNum materialNum
    FROM OfferInstance, OfferToProduct, Product
    WHERE Product.productId = OfferToProduct.productId
    AND OfferToProduct.offerId = OfferInstance.offerId
    AND OfferInstance.offerCode = offer_code_in
    DO
    INSERT INTO NEWTABLE VALUES (offer_code_in, materialNum);
    END FOR;
    END
    @
    -------------------------------------------------------------------
    Note: I haven't tested it. But believing that it will work.

    Please let me know the results.

    With Regards,
    Jake

  10. #10
    Join Date
    Feb 2004
    Posts
    7
    Here is the solution we eventually came up with. Thanks everyone for your help.

    CREATE PROCEDURE sp_load_products (IN In_Offer_Code char(6))
    LANGUAGE SQL
    BEGIN

    -- Declare Variables
    DECLARE strSQLStatement VARCHAR(10000);
    DECLARE strCondition VARCHAR(2050);
    DECLARE strWhereClause VARCHAR(10000);
    DECLARE intConditionRecCnt INTEGER;

    -- Declare Condition Handlers
    DECLARE not_found CONDITION FOR SQLSTATE '02000';
    DECLARE CONTINUE HANDLER FOR not_found
    SET intConditionRecCnt = 1;

    -- Select Offer-To-Product Condition Records for the Offer-Instance Record
    SET intConditionRecCnt = 0;
    FOR v_row AS SELECT REPLACE(op.CONDITION, 'ProductID','MATERIALNUM') as SQLCondition
    FROM OFFERTOPRODUCT op
    WHERE op.OFFERID = (SELECT oi.OFFERID
    FROM OFFERINSTANCE oi
    WHERE oi.OFFERCODE = In_Offer_Code and oi.TEMPFLAG = 0 and op.CONDITION <> '')
    DO
    SET intConditionRecCnt = intConditionRecCnt + 1;
    SET strCondition = '(' || v_row.SQLCondition || ')';
    IF intConditionRecCnt = 1 THEN
    SET strWhereClause = strCondition;
    ELSE
    SET strWhereClause = strWhereClause || 'OR' || strCondition;
    END IF;

    END FOR;

    -- Build Dynamic Query and Insert Records Into OFFERPRODUCT
    IF strWhereClause <> '' Then
    SET strSQLStatement = 'INSERT INTO OFFERPRODUCT (SELECT ' || '''' || In_Offer_Code || '''' || ',' || '''' || In_Contact_Date || '''' || ',MATERIALNUM, CURRENT TIMESTAMP FROM PRODUCT WHERE ' || strWhereClause || ')';
    EXECUTE IMMEDIATE strSQLStatement;
    END IF;

    END

Posting Permissions

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