Have you considered writing a UDF to hide all the messy calculations? Our ERP uses CYMD and, sometimes, 9999999 to indicate "forever". My UDF handles that - substituting a known hi or low date for invalids.
-----Original Message-----
From: MIDRANGE-L [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Lynne Noll
Sent: Monday, April 16, 2018 6:32 PM
To: Midrange Systems Technical Discussion
Subject: Re: Query Puzzler After i 7.1 to i 7.2 upgrade.
RPG does IF statements in the order you type them, but SQL does not, necessarily. Parts of it run in different threads.
However, I generally convert from the known to be valid current_date to either string or number when I am comparing to dates that do not exist as real date fields.A lot of legacy data has date fields for which zeros, nines,, and sometimes 02/31/18 is expected data. And nothing in the data base enforces the date validity of numeric and string dates. So I routinely go from a good date (from current date) to either string or number.
Often, I use multiplication/addition, and keep it numeric (I deal with a lot of c, yy, mm, dd dates). Year*10000+month*100+day works fine in date comparisons, and there are functions to pull out the year, month, and date from the current date or dates calculated from it.
Sent from Outlook<
http://aka.ms/weboutlook>
________________________________
From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxx> on behalf of DrFranken <midrange@xxxxxxxxxxxx>
Sent: Monday, April 16, 2018 8:05 PM
To: Midrange Systems Technical Discussion
Subject: 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
--
- Larry "DrFranken" Bolhuis
www.Frankeni.com<
http://www.Frankeni.com>
www.iDevCloud.com<
http://www.iDevCloud.com> - Personal Development IBM i timeshare service.
www.iInTheCloud.com<
http://www.iInTheCloud.com> - Commercial IBM i Cloud Hosting.
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit:
https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at
https://archive.midrange.com/midrange-l.
Please contact support@xxxxxxxxxxxx for any subscription related questions.
Help support midrange.com by shopping at amazon.com with our affiliate link:
http://amzn.to/2dEadiD
midrange.com
