AW: Calculations in a stored procedure?

["BirgittaHauser" <Hauser@xxxxxxxxxxxxxxx>]

Hi,

Even if it is no RPG in pure SQL the whole Declare Section (all Declare
Commands including Declare cursors) must preceed any other statements.

There is even a predefined sequence within the declare statements:
1. Define Variables
2. Define Conditions
3. Define Return Codes
4. Define Cursors
5. Define Condition/Exit/Undo Handlers

Change your procedure body at follows at your procedure will ge created.
(Even though I cannot see where you are using the 
Worksformdate and workthrudate.)

begin
  declare workfromdate int;
  declare workthrudate int;

  declare c1 cursor with return for
  select dmekcd as partnumber, dmehcd as stockloc,
         dmcdcd as transcode, sum(dmgzfq) as quantity,
         idate(dmatdt,'*CYMD')
  from vngdbdta/dbdmrep /* Inventory transactions */
  where dmazcd = 'WV' /* Water Valley location */
    and dmatdt between workfromdate and workthrudate
    and dmcdcd = 'MRCT' /* Transaction code */
    and dmehcd = 'SH'
  group by dmekcd, dmehcd, dmcdcd, dmatdt
  having sum(dmgzfq) <> 0
  order by dmatdt, dmekcd;

  set workfromdate =
      converttoidate(idate(fromdate,'*mdccyy'),'*CYMD');

  set workthrudate =
      converttoidate(idate(thrudate,'*mdccyy'),'*CYMD');

open c1;

end


Mit freundlichen Grüßen / Best regards

Birgitta Hauser

"Shoot for the moon, even if you miss, you'll land among the stars." (Les
Brown)
"If you think education is expensive, try ignorance." (Derek Bok)
"What is worse than training your staff and losing them?  Not training them
and keeping them!"

-----Ursprüngliche Nachricht-----
Von: midrange-l-bounces+hauser=sss-software.de@xxxxxxxxxxxx
[mailto:midrange-l-bounces+hauser=sss-software.de@xxxxxxxxxxxx] Im Auftrag
von lgoodbar@xxxxxxxxxxxxxx
Gesendet: Tuesday, December 11, 2007 18:47
An: midrange-l@xxxxxxxxxxxx
Betreff: RE: Calculations in a stored procedure?


This is a pure SQL procedure, not RPG. I wanted to keep it simple (one
object), but will go the RPG route (external stored procedure) if I must.

Loyd Goodbar
Senior programmer/analyst
BorgWarner
TS Water Valley
662-473-5713
-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Booth Martin
Sent: Tuesday, December 11, 2007 11:45
To: Midrange Systems Technical Discussion
Subject: Re: Calculations in a stored procedure?

Have you thought of something like this: 

   workfromdate = %editw(%date('MM/DD/YYYY': *MDY): *YMD): '  /  /    ')

or something like that.  :)