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

Well, this afternoon, I found my way into "Run SQL Scripts" and Visual Explain, and about an hour and a half ago, I found out that I had initially been given the wrong BIRT report, and I also found out how to get to the underlying query.

The query is actually 3 queries, UNION ALL'd together (not quite sure why UNION'd together isn't good enough).

Feeding the underlying query into "Run SQL Scripts" in a model environment on our local system, I found that Visual Explain produced a fairly substantial tree. Comparing censored and uncensored views, I found that the censored view added 3 nodes ("complicated," "logic," and "nested loop join") to the tree. I'm not entirely sure how the nested loop join fits in.

The relevant part of what is a very complicated query (the names have been changed to protect the innocent) 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

(Incidentally, the last line of the WHERE clause appears to be completely redundant.)

I'm wondering why a simple WHERE clause of
> WHERE CENSORACCT(ACCTID) = 'P'
in the ACCOUNTVIEW definition, where CENSORACCT is an RPG program that has no SQL whatsoever, causes a nested loop join.

I'm also wondering why I'm getting the full read of the entire contents of ACCOUNTVIEW, and whether it's happening here, or in the other branch of the UNION ALL that references that view (and joins to it).

I'll have more time to investigate further on Monday.

--
JHHL