Data Archival selection criteria: less than January 1 of x years ago

, , ,
1

One enhancement had been made for a use case we had at the time where if we were running an archival extraction, and we wanted to archive 3 years ago or more if it was before the 1st of the month, we could specify this (AI is a D format - P4 in ADABAS - field):

EXTRACT fin_trans_xref_prod(fin_trans_xref_prod.AI < TODAY(-3Y,DATX,”DD=01”))

Now we have a use case where we want to only extract records if it was before January 1, three years ago. I have tried all kinds of things like (AB is a D format - P4 in ADABAS - field):

EXTRACT sp_adjustments_prod(sp_adjustments_prod.AB < TODAY(-3Y,DATX,”MM=01,DD=01"))
EXTRACT sp_adjustments_prod(sp_adjustments_prod.AB < TODAY(-3Y,DATX,”MM=01 DD=01"))
EXTRACT sp_adjustments_prod(sp_adjustments_prod.AB < TODAY(-3Y,DATX,”MM=01",“DD=01”))
EXTRACT sp_adjustments_prod(sp_adjustments_prod.AB < TODAY(-3Y,DATX,”MMDD=0101"))

I cannot figure out how to do this, and the ADRExtractionSyntaxReference.pdf doesn’t seem to give any clues. Is this even possible? Seems this would be a common archival rule to apply.

Please advise.

Thanks,

Brian

2

Helps if I use single quotes inside the selection criteria. It accepted this:

EXTRACT sp_adjustments_prod(sp_adjustments_prod.AB < TODAY(-3Y,DATX,‘MM=01,DD=01’))

3

Hi Brian,
Double quotes are the correct way to define the selection criteria.

So the following should be ok:
EXTRACT sp_adjustments_prod(sp_adjustments_prod.AB < TODAY(-3Y,DATX,“MM=01,DD=01”))

Looking closely at your failing syntax examples, it looks like the double quote which precedes the MM= parameter is of a different (wrong) format to the other (correct) double quotes eg. the one following the DD=01 parameter…

I believe this may be the cause of the problem.