Natural DB2 Subtract 1 Year from Variable

, , ,
1

In our nightly batch processing, our shop reads the current day’s date, as of 7PM, from a date file so that if we run past midnight, the posting date will still be from the previous day. In one of our programs, we’re reading in the date from the date file and subtracting 1 year from the date. I’m having issues with the DB2 statement in Natural.

We move the date from the date file to #CURRENT-DATE(A10) with a format of YYYY-MM-DD. I have #CURR-DT-QUOTES defined as (A12).

Here’s my code. The SQL statement works fine in DbVisualizer, but I’m getting error -418 with SQLSTATE 42610 when I run my batch Natural program.


COMPRESS "'" #CURRENT-DATE "'" INTO #CURR-DT-QUOTES LEAVING NO SPACE
WRITE '=' #CURR-DT-QUOTES 
*
SELECT *
FROM MYTABLE
WHERE POST_DT >= DATE(:#CURR-DT-QUOTES) -1 YEAR

Output:

#CURR-DT-QUOTES: ‘2019-12-23’
NAT3700 Error -418 with SQLSTATE 42610 from call to DB2

I looked up the error and SQLSTATE definitions.

Error -418: A STATEMENT STRING TO BE PREPARED CONTAINS AN INVALID USE OF PARAMETER MARKERS
SQLSTATE 42610: A parameter marker or the null value is not allowed.

Is that referring to the quotes around the date? Thanks for any help you can give.

2

Not being an sql person, some suggestions:

First, check on the syntax for the DATE function. Assuming it is correct (is there supposed to be a colon);

Second, after the WRITE statement have something like:

subtract 1 from #year /* which is a redefine of YYYY in #CURR-DT-QUOTES
WRITE ‘=’ #curr-dt-quotes

then try the following

WHERE POST_DT >= DATE(:#CURR-DT-QUOTES)

3

I was able to finally get it to work by adding the TO_DATE function. Here’s my test code to make sure it gave the correct result for a leap year.


DEFINE DATA LOCAL                                                    
01 #CURR-DT-D      (D) INIT <D'02/29/2020'>                          
01 #CURR-DT-ALPHA  (A10)                                             
01 #CURR-DT-QUOTES (A12)                                             
01 #YEAR-AGO       (A10)                                             
END-DEFINE                                                           
MOVE EDITED #CURR-DT-D(EM=YYYY-MM-DD) TO #CURR-DT-ALPHA              
COMPRESS "'" #CURR-DT-ALPHA "'" INTO #CURR-DT-QUOTES LEAVING NO SPACE
WRITE '=' #CURR-DT-QUOTES                                            
SELECT DATE(TO_DATE(:#CURR-DT-QUOTES,'YYYY-MM-DD')) - 1 YEAR         
  INTO #YEAR-AGO                                                     
FROM MY_TABLE
END-SELECT                                                           
WRITE '=' #YEAR-AGO                                                  
**                                                                   
END

Results:
#CURR-DT-QUOTES: ‘2020-02-29’
#YEAR-AGO: 2019-02-28