Query Puzzler After i 7.1 to i 7.2 upgrade.

Cutomer has at least 20 traditional QU1 queries that ran perfectly on i 7.1 and all fail on i 7.2. The error it throws is QRY2283 Selection error and it happens nearly instantly on every single query.

The primary file used by the query has the date broken into four fields: 1 digit century, 2 digit year, 2 digit month, 2 digit day, all numeric. From a data perspective having all four of those fields have a zero value is legitimate.

Four files are joined to this primary file with option 2 (Match with primary.)

A construct is used to create a date from those fields thus:

Field Expression
QRYDATE date(digits(month)||'/'||
digits(day)||'/'||
digits(year))

And to create yesterdays date thus:

Field Expression
YESTERDAY current(date) - 1 day

The selection criteria looks like this:

AND/OR Field Test Value
FLAG1 NE 'Y'
AND FIELD1 LIKE 'Z%'
AND QRYDATE EQ yesterday

Note that there are NO rows where FLAG1 is not 'Y' AND FIELD1 starts start with a 'Z' that have zero dates. Said another way, in all cases where the row is selected by the first two criteria here, QRYDATE will be a valid date.

IBM Basically has told us. "Yep might not work. Might have failed in i 7.1 and might fail in i 7.2. Do it differently." I realize that CQE was replaced with SQE but how does this change make it fail? IBM says "We don't know. We only know that if QRYDATE is not a valid date (e.g. 00/00/00) the query can fail.

How can I accept this answer?

How would you fix this?

(Scroll down for my solution to the problem. The customer is still validating my solution.)

.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.

..

Effectively I changed QRYDATE to be just a string and YESTERDAY as well, thus:

Field Expression
QRYDATE digits(month)||'/'||
digits(day)||'/'||
digits(year)

And to create yesterdays date thus:

Field Expression
YESTERDAY CHAR(current(date) - 1 day)

I believe this works because the value 00/00/00, which should never be used, is valid as a string value.

- L