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

On 3/4/13 3:07 PM, Roger Harman wrote:

I haven't followed this in great detail but, if you want to limit the search
to a particular user first, perhaps you could try a CTE and use that as the
source for the balance of your query.

It took me a while to find out what a CTE is, and a while to find out how to use one.

Something like:

with USA as (select * from SCHEDACTVIEW where wtactsv1.user# = 2246)
select
ACCOUNTVIEW.ACCOUNT_NAME,ACCOUNTVIEW.ACCOUNT_TYPE,
'' as FIRST_NAME,'' as LAST_NAME,
USA.SCHED_DATE,USA.ACTIVITY_ID,USA.CATEGORY,USA.REGARDING,USA.USER#,USA.TYPE,USA.PRIORITY

from USA join ACCOUNTVIEW on USA.RECORD_ID=ACCOUNTVIEW.ACCOUNT_ID

is what you meant?

The Visual Explain looks more complicated, but the result is exactly the same: a query that produces an empty result set still manages to visit every single record of ACCOUNTVIEW, for no reason at all.

--
JHHL