Try using CURRENT DATE instead of the date function.
>From: Steve Rogers
>Reply-To: sqr-users@list.iex.net
>To: SQR-USERS@list.iex.net
>Subject: use of curdate within MVS - should it work?
>Date: Tue, 10 Apr 2001 13:14:11 -0400
>
>We are in the process of converting AS-400 version 7.5 of PeopleSoft HRMS to
>MVS. While testing an SQR containing the following code -
>
> FROM PS_JOBCODE_TBL JC
> WHERE JC.SETID = SETID !
> AND JC.JOBCODE like 'K%'
> AND JC.EFFDT = (SELECT MAX(JC1.EFFDT)
> FROM PS_JOBCODE_TBL JC1
> WHERE JC1.JOBCODE = JC.JOBCODE
> AND JC1.EFFDT <= curdate() )
> AND JC.EFF_STATUS = 'A'
>
> I received an error that indicated that MVS could not handle curdate. Here
>is the error
>
>(SQR 5528) DB2 SQL PREPARE/DECLARE error -440 in cursor 5:
> DSNT408I SQLCODE = -440, ERROR: NO FUNCTION BY THE NAME CURDATE HAVING
>COMPATIBLE ARGUMENTS WAS FOUND IN THE CURRENT PATH
>
> DSNT418I SQLSTATE = 42884 SQLSTATE RETURN CODE
> DSNT415I SQLERRP = DSNXORFN SQL PROCEDURE DETECTING ERROR
> DSNT416I SQLERRD = -100 0 0 -1 0 0 SQL DIAGNOSTIC INFORMATION
> DSNT416I SQLERRD = X'FFFFFF9C' X'00000000' X'00000000' X'FFFFFFFF' X'
> INFORMATION
>
> Can anyone tell me why this is happening and how to resolve it?
>
> thanks,
> Steve
Get your FREE download of MSN Explorer at http://explorer.msn.com