× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



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




As an Amazon Associate we earn from qualifying purchases.

This thread ...

Follow-Ups:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.