RE: Query Puzzler After i 7.1 to i 7.2 upgrade.

["Musselman, Paul" <pmusselman@xxxxxxxxxxxxxxxx>]

One of the rules of database design is 'only use a field for 1 purpose.'  Dates of 00/00/00 and 99/99/99 are flags meaning something other than a date!

There should have been fields for 'never expires' and 'no date provided.'   I think that would go hand in hand with null values for the date field...  but what do you do if you can't have nulls??

Paul E Musselman
PaulMmn@xxxxxxxxxxxxx

-----Original Message-----
From: MIDRANGE-L [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of DrFranken
Sent: Tuesday, April 17, 2018 12:15 AM
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>
Subject: Re: Query Puzzler After i 7.1 to i 7.2 upgrade.

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)