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)

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;

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?

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)

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.

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

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.

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.

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