Your date problem seems solved. Just wanted to point out that the subselect was incorrect in that the setid was omitted. If you are using multiple setid's, you are screwed.
Jeff Berkhimer, Ogden Corp.
----Original Message Follows----
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