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:
???
How you make loop ? How you exit from loop ?

Lenny

Told you I was new to this

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

The more I search for information the more I get confused

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

You don't need any cursors.

Following query will do for you what do you need:

That's it ! You have to understand DB2 and all become easy....

Lenny

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

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.

Thank you so much I have been able to create the procedure, appreciate the help.

Actually, that's basic/standard SQL.