RE: More still, Re: Questions about RPG service program as an SQL UDF

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