Re: use of curdate within MVS - should it work?

[csmyth <csmyth@IDIRECT.COM>]

That piece of the code to retrieve the date from the database is database 
specific.

What database engine were you using on the AS-400? Is it the same as on the MVS?
Same version?

In SQL Server in that cicumstance you would use Getdate() and in Oracle is is
something different that I don't remember off hand. Find out what you would use 
in
DB2 and that should be what you put in the SQR.

Cameron

Steve Rogers wrote:

> 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