Friendlier way to create stored procedures in Navigator?

I've created many stored procedures in SQL Server, and need to create a
read stored procedure in DB2 for use in a .NET application. I'm new to
writing stored procedures on DB2. Using iSeries Navigator to create a
new stored procedure. After entering my statements, I always get this
error:



Token ; was not valid. Valid tokens: ...

Message ID: SQL0104

Cause: A syntax error was detected at token ;....



But there are no line number references or highlighting where the error
might have occurred. Any help in diagnosing this is appreciated!

The procedure reads a DDS-defined file, whose dates are CYMD numeric
7,0. Converting the transaction "date" to a date for the .NET
application.





Here are the various tabs:

General tab

Maximum number of result sets: 1

Data access: Reads SQL data



Parameters tab

FromDate CHARACTER 10 IN

ThruDate CHARACTER 10 IN



SQL Statements tab

begin



declare workfromcymd int;

declare workthrucymd int;



declare workc int;

declare workyear char(4);

declare workmonth char(2);

declare workday char(2);



/* Convert fromdate from character format MM/DD/YYYY to CYYMMDD */

set workmonth = substring(fromdate,1,2);

set workday = substring(fromdate,4,2);

set workyear = substring(fromdate,7,4);

set workc = int(workyear)-1900;

if workc < 0 then;

set workc = 0

end;



set workfromcymd = (workc * 1000000) +

(int(workyear)*10000) +

(int(workmonth)*100) +

(int(workday));



/* Convert thrudate from character format MM/DD/YYYY to CYYMMDD */

set workmonth = substring(thrudate,1,2);

set workday = substring(thrudate,4,2);

set workyear = substring(thrudate,7,4);

set workc = int(workyear)-1900;

if workc < 0 then;

set workc = 0

end;



set workthrucymd = (workc * 1000000) +

(int(workyear)*10000) +

(int(workmonth)*100) +

(int(workday));



/* Finally! */

select dmekcd as partnumber, dmehcd as stockloc,

dmcdcd as transcode, sum(dmgzfq) as quantity,

date(

trim(char(int((dmatdt/10000)+1900))) concat '-' concat

substring(char(dmatdt),4,2) concat '-' concat

substring(char(dmatdt),6,2)

)

from vngdbdta.dbdmrep /* Inventory transactions */

where dmazcd = 'WV' /* Water Valley location */

and dmatdt between workfromcymd and workthrucymd /* Transaction date */

and dmcdcd = ';MRCT' /* Transaction code */

and dmehcd = 'SH' /* To stock location */

group by dmekcd, dmehcd, dmcdcd, dmatdt

having sum(dmgzfq) <> 0

order by dmatdt, dmekcd



end









Loyd Goodbar

Senior programmer/analyst

BorgWarner

TS Water Valley

662-473-5713