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