Pass PARM from JCL to Easytrieve SQL WHERE clause
Posted: Tue Oct 24, 2023 8:32 am
I have an Easytrieve that queries a database and puts the result in a report. I need to filter the information in the query by taking the Order Date from Control-M. For that I'm passing the %%$ODATE from the JCL by using utility EZTPX01; I later put that date in a working storage variable, format it and then pass it to the WHERE clause of the query. Before this change I used TIMESTAMP(CURRENT DATE) in the aforementioned clause, and it worked beautifully.
Now I can't make it work, I keep getting SQL errors, depending on how I pass the WS variable (with or without quotes, using TO_CHAR, TO_DATE, TIMESTAMP, and others):
*******A046 SQL - DSNT408I SQLCODE = -180, ERROR: THE DATE, TIME, OR TIMESTAMP VALUE *N IS INVALID
*******B204 SQL - DSNT408I SQLCODE = -206, ERROR: ODATE IS NOT VALID IN THE CONTEXT WHERE IT IS USED
The PARM passed from the JCL is read like this: 20231023.
I can't share the program for compliance reasons, but if there's anything you need please let me know. This is a general layout of the code:
I'm using the SQL INCLUDE statement to retrieve database information; the JOB activity section looks like this:
JOB INPUT SQL +
START 1000-PARMS-INIT +
FINISH 9999-END-OF-JOB
Here's the date processing:
1000-PARMS-INIT. PROC
CALL EZTPX01 USING (PARM-REGISTER XPARM-INFO)
ODATE-YYYY = XDATE-YYYY
ODATE-MM = XDATE-MM
ODATE-DD = XDATE-DD
ODATE-SEP1 = '-'
ODATE-SEP2 = '-'
END-PROC.
*
And this is the WHERE clause:
FROM BOACH0.PEP_OPERATOR_MAINTENANCE_AUDIT +
WHERE ACTION_TIMESTAMP < +
TIMESTAMP(ODATE) +
* ACTION_TIMESTAMP is the date field from the database in TIMESTAMP(6)
I've tried to solve this using the manual, going over forums and just can't figure this out. I'm sure I'm overlooking something.
I will take any help the experts can provide, thank you in advance
Now I can't make it work, I keep getting SQL errors, depending on how I pass the WS variable (with or without quotes, using TO_CHAR, TO_DATE, TIMESTAMP, and others):
*******A046 SQL - DSNT408I SQLCODE = -180, ERROR: THE DATE, TIME, OR TIMESTAMP VALUE *N IS INVALID
*******B204 SQL - DSNT408I SQLCODE = -206, ERROR: ODATE IS NOT VALID IN THE CONTEXT WHERE IT IS USED
The PARM passed from the JCL is read like this: 20231023.
I can't share the program for compliance reasons, but if there's anything you need please let me know. This is a general layout of the code:
I'm using the SQL INCLUDE statement to retrieve database information; the JOB activity section looks like this:
JOB INPUT SQL +
START 1000-PARMS-INIT +
FINISH 9999-END-OF-JOB
Here's the date processing:
1000-PARMS-INIT. PROC
CALL EZTPX01 USING (PARM-REGISTER XPARM-INFO)
ODATE-YYYY = XDATE-YYYY
ODATE-MM = XDATE-MM
ODATE-DD = XDATE-DD
ODATE-SEP1 = '-'
ODATE-SEP2 = '-'
END-PROC.
*
And this is the WHERE clause:
FROM BOACH0.PEP_OPERATOR_MAINTENANCE_AUDIT +
WHERE ACTION_TIMESTAMP < +
TIMESTAMP(ODATE) +
* ACTION_TIMESTAMP is the date field from the database in TIMESTAMP(6)
I've tried to solve this using the manual, going over forums and just can't figure this out. I'm sure I'm overlooking something.
I will take any help the experts can provide, thank you in advance