Pass PARM from JCL to Easytrieve SQL WHERE clause



Unicenter CA-Easytrieve Plus Report Generator: CA's information retrieval and data management tool

Pass PARM from JCL to Easytrieve SQL WHERE clause

Postby MemoPerez » 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 :D
MemoPerez
 
Posts: 7
Joined: Sat Nov 19, 2022 1:27 am
Has thanked: 0 time
Been thanked: 0 time

Re: Pass PARM from JCL to Easytrieve SQL WHERE clause

Postby MemoPerez » Tue Oct 24, 2023 11:16 pm

Forgot to add the working storage:
*                                                    
* DATE FILTER                                        
XPARM-INFO                                    W  10 A
XPARM-LENGTH         XPARM-INFO                   2 B
XPARM-DATE           XPARM-INFO               +2  8 A
*                                                    
XDATE-YYYY           XPARM-DATE                   4 A
XDATE-MM             XPARM-DATE               +4  2 A
XDATE-DD             XPARM-DATE               +6  2 A
*                                                    
ODATE                                         W  10 A
ODATE-YYYY           ODATE                        4 A
ODATE-SEP1           ODATE                    +4  1 A
ODATE-MM             ODATE                    +5  2 A
ODATE-SEP2           ODATE                    +7  1 A
ODATE-DD             ODATE                    +8  2 A
MemoPerez
 
Posts: 7
Joined: Sat Nov 19, 2022 1:27 am
Has thanked: 0 time
Been thanked: 0 time

Re: Pass PARM from JCL to Easytrieve SQL WHERE clause

Postby MemoPerez » Fri Oct 27, 2023 1:13 am

I got a fix for this:

I changed the format of the DB2 column 'ACTION_TIMESTAMP' by using TO_CHAR(ACTION_TIMESTAMP, 'YYYY-MM-DD') in the query, so that when the WHERE clause kicks in, it can compare ODATE (which is a string) with the formatted ACTION_TIMESTAMP.

Not necessarily the thing I was looking for to get (code wise), but it definitely works to achieve my objective.

Thanks anyway :)
MemoPerez
 
Posts: 7
Joined: Sat Nov 19, 2022 1:27 am
Has thanked: 0 time
Been thanked: 0 time


Return to CA-Easytrieve

 


  • Related topics
    Replies
    Views
    Last post