Calculations in a stored procedure?

[<lgoodbar@xxxxxxxxxxxxxx>]

In a stored procedure, I need to convert a passed character string date
(MM/DD/YYYY format) into CYYMMDD for the select statement. I'm using
Alan Campin's iDate utility for this. When I try to create the
procedure, I get error "Token C1 was not valid. Valid tokens: GLOBAL."

 

However, if I comment out the lines

/*set workfromdate =

      converttoidate(idate(fromdate,'*mdccyy'),'*CYMD');

  set workthrudate =

      converttoidate(idate(thrudate,'*mdccyy'),'*CYMD'); */

 

it compiles but is useless without the initial date conversions.

 

I also tried:

Select converttoidate(idate(fromdate,'*mdccyy'),'*CYMD')

Into workfromdate

> From sysibm/sysdummy1;

 

And got the same error token C1 was not valid.

 

Note: The procedure works if the date conversion is inline in the where
clause; however it takes about 10 times longer to run. Hence, the move
to outside the select. I've got to be doing something wrong but can't
see what. Presumably stored procedures support some calculations prior
to returning a result set?

 

create procedure vngcusdta/uphretpart

  ( in fromdate char(10), in thrudate char(10) )

  result set 1

  language sql

  not deterministic

  reads sql data

  set option dbgview=*list

begin

  declare workfromdate int;

  declare workthrudate int;

  set workfromdate =

      converttoidate(idate(fromdate,'*mdccyy'),'*CYMD');

  set workthrudate =

      converttoidate(idate(thrudate,'*mdccyy'),'*CYMD');

  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;

open c1;

end

 

 

Loyd Goodbar

Senior programmer/analyst

BorgWarner

TS Water Valley

662-473-5713