× 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.



I'm wondering if this has to do with how your JOIN is defined.

What is you did the following instead:

from SCHEDACTVIEW join ACCOUNTVIEW
on SCHEDACTVIEW.RECORD_ID=ACCOUNTVIEW.RECORD_ID ***I changed this from ACCOUNT_ID b/c I thought it might be a typo***
and SCHEDACTVIEW.ACCOUNT_ID=ACCOUNTVIEW.ACCOUNT_ID
where SCHEDACTVIEW.USER# = ?

It should logically be the same, but due to the UDF I'm not sure if the optimizer will see it as the same.


-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx [mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of James H. H. Lampert
Sent: Monday, March 04, 2013 12:10 PM
To: RPG programming on the IBM i (AS/400 and iSeries)
Subject: More still, Re: Questions about RPG service program as an SQL UDF

Ok.

For reference, the "problem" branch of that 3-way UNION ALL is

select ACCOUNTVIEW.ACCOUNT_NAME,ACCOUNTVIEW.ACCOUNT_TYPE,
'' as FIRST_NAME,'' as LAST_NAME,
SCHEDACTVIEW.SCHED_DATE,SCHEDACTVIEW.ACTIVITY_ID,
SCHEDACTVIEW.CATEGORY,SCHEDACTVIEW.REGARDING,SCHEDACTVIEW.USER#,
SCHEDACTVIEW.TYPE,SCHEDACTVIEW.PRIORITY

from SCHEDACTVIEW, ACCOUNTVIEW
where SCHEDACTVIEW.USER# = ?
and SCHEDACTVIEW.RECORD_ID=ACCOUNTVIEW.ACCOUNT_ID
and SCHEDACTVIEW.ACCOUNT_ID=ACCOUNTVIEW.ACCOUNT_ID

which finds "scheduled activities" that have an "account" but no "contact." (Once again, the names have been changed to protect the
innocent.)

Last week, I learned that if I swap in a view that had a diagnostic version of the censorship routine, I'd get one complete trip thruough the censored file, plus one access of each "scheduled activity" found by the query.

Today, after much futzing around, I was able to verify that the above-quoted branch is the problem: each of the "scheduled activities"
actually found by the query came from the third branch, which looks for activities that have both an "account" and a "contact," and involves 4 views (the two referenced above and two more) in the FROM clause.

So the "problem" branch of the query doesn't find a single record that satisfies the first part of its WHERE clause. And yet it looks at every single record in SCHEDACTVIEW, while the more complicated branch doesn't.

That doesn't make any sense at all.

I don't suppose iNav "Run Sql Scripts" will allow me to connect to a database on a box to which iNav itself doesn't have a connection . . .



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.