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

That's the gist of it. Also, Vern had a good explanation and suggestions.

Are you saying that you KNOW there should be a result set and you're not
getting any records returned? I used to tell a programmer who worked for me
to use the "how do you eat an elephant?" methodology. Break the complex
query down to bite-size pieces using temp tables for results and to feed the
next portion. Then, you can see where things fall apart.

Is there an index on USER# and another on ACCOUNTVIEW.ACCOUNT_ID?


-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx [mailto:rpg400-l-bounces@xxxxxxxxxxxx]
On Behalf Of James H. H. Lampert
Sent: Monday, March 04, 2013 3:45 PM
To: RPG programming on the IBM i (AS/400 and iSeries)
Subject: 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#,US
A.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

--
This is the RPG programming on the IBM i (AS/400 and iSeries) (RPG400-L)
mailing list To post a message email: RPG400-L@xxxxxxxxxxxx To subscribe,
unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives at
http://archive.midrange.com/rpg400-l.