× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



IBM Suggested that as well but it doesn't work. Apparently some under the covers stuff even in CQE has changed enough to cause our issue.

It appears we were OK on i 7.1 because the date was never evaluated when it would have been invalid. Now it gets evaluated in all cases even when the row will not be selected.

As was suggested by Lynne changing the concat to create a simple string field and the 'yesterday' date into a string field also allows "00/00/00" without error as it's just a string now not a date.

This would be an issue if we were comparing with less than or greater than but these queries all query for one specific date so it works.

At this point that is the solution being implemented.

- Larry "DrFranken" Bolhuis

www.Frankeni.com
www.iDevCloud.com - Personal Development IBM i timeshare service.
www.iInTheCloud.com - Commercial IBM i Cloud Hosting.

On 4/16/2018 11:57 PM, Birgitta Hauser wrote:
You can reroute the queries to the CQE, by setting the SQE_NATIVE_ACCESS
option in the QAQQINI file to *NO.
Copy the QAQQINI File with CRTDUPOBJ (not CPYF!!!) into any library you
want.
Modify the SQE_NATIVE_ACCESS option (with SQL).
Associate the modified QAQQINI file to you job by executing the CHGQRYA CL
command.
CHGQRYA QRYOPTLIB(YOURLIB)

Mit freundlichen Grüßen / Best regards

Birgitta Hauser

"Shoot for the moon, even if you miss, you'll land among the stars." (Les
Brown)
"If you think education is expensive, try ignorance." (Derek Bok)
"What is worse than training your staff and losing them? Not training them
and keeping them!"
„Train people well enough so they can leave, treat them well enough so they
don't want to.“ (Richard Branson)


-----Original Message-----
From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxx> On Behalf Of DrFranken
Sent: Dienstag, 17. April 2018 02:05
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>
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



As an Amazon Associate we earn from qualifying purchases.

This thread ...

Follow-Ups:
Replies:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.