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

Can you just encapsulate your v2 within a cte and then build a new v2 off that, instead of v3:

Create view v2new
as
With v2cte as (select ...statement you used to build original v2 ...)

Select * from v2cte where censored_data = stuff
;


-Tom Stieger
IT Manager
California Fine Wire


-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx [mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of James H. H. Lampert
Sent: Tuesday, March 05, 2013 3:29 PM
To: RPG programming on the IBM i (AS/400 and iSeries)
Subject: Re: BREAKTHROUGH, Re: Questions about RPG service program as an SQL UDF

On 3/5/13 7:43 AM, Vernon Hamberg wrote:

Most excellent!

The only downside is that while the new view I developed works very fast in BIRT reports, it's also rather slow (censorship or not) if you try to look at it in QuestView (or presumably WRKDBF, DBU, or any other native-RLA-based file access utility). Not that anybody is ever likely to need to do that on a regular basis.

I'd be interested in ideas on how to refine this into a more mature solution, maybe one that's built directly on the PFs, rather than on top of existing VIEWs. Also, is there any way I could add the censorship directly to what I refer to here as the "V2" view, rather than having to build a "V3" view on top of it, to add the censorship?

--
JHHL

On 3/4/2013 6:54 PM, I wrote:

I think I just cut the proverbial Gordian Knot here.

Instead of having this obscenely huge query with the 3-pronged UNION
ALL in the BIRT report definition, accessing a censored view, we put
it in its own VIEW definition (call it SCHEDACTV2), use an uncensored
version of ACCOUNTVIEW, and we add SCHEDACTVIEW.ACCOUNT_ID to its
SELECT (so it can be censored). Then, we create another VIEW
definition, SCHEDACTV3, that applies the censorship function to it.

Then, the query in the BIRT report becomes SELECT * FROM SCHEDACTV3
WHERE USER# = ?

And the result of all this?

A really ugly Visual Explain, when I run it locally in iNav.

And a response time that drops from 20+ minutes to under 8 seconds,
when I put it on the customer box, and run the BIRT report.

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