Re: have View need Stored Proc

[Hoteltravelfundotcom <hoteltravelfun@xxxxxxxxx>]

the issue is, there is a massive amount of data. I asked the user and she
said most of the time they run the report for periods over the past 2 years
but sometimes it can be more past dates.
So I was thinking of creating one view for past 2 years and another view
for previous years.

Wouldn't it be faster that way? Currently, it takes about 30+ minutes to
run the report. All the Links are being done in the Reporting tool, it
queries 5 Logical files on the IBM i.

Since I now have 1 View for all these, I also want to see if other formulas
the tool is doing might be better to be done in the View. But still I
anticipate this as time taking.


On Tue, Sep 17, 2013 at 8:51 AM, Hoteltravelfundotcom <
hoteltravelfun@xxxxxxxxx> wrote:

> I see, this is good. But I was thinking of the time taking to read. I
> thought it would be faster as a stored procedure.
>
>
> On Mon, Sep 16, 2013 at 7:00 PM, Birgitta Hauser <Hauser@xxxxxxxxxxxxxxx>wrote:
>
> > Why not simply adding a WHERE clause from within the reporting tool that
> > selects the records with the desired dates from the view?
> >
> > Select * from View
> > Where IDDOCD between FirstDate and LastDate;
> >
> > Mit freundlichen Grüßen / Best regards
> >
> > Birgitta Hauser
> >
> > "Shoot for the moon, even if you miss, you'll land among the stars." (Les
> > Brown)
> > "If you think education is expensive, try ignorance." (Derek Bok)
> > "What is worse than training your staff and losing them? Not training them
> > and keeping them!"
> >
> >
> > -----Ursprüngliche Nachricht-----
> > Von: midrange-l-bounces@xxxxxxxxxxxx
> > [mailto:midrange-l-bounces@xxxxxxxxxxxx] Im Auftrag von
> > Hoteltravelfundotcom
> > Gesendet: Monday, 16.9 2013 22:21
> > An: Midrange Systems Technical Discussion
> > Betreff: have View need Stored Proc
> >
> > I did finally get that view to work. What I would like to do, and I am not
> > sure how, is to make a stored procedure so that this view will accept
> > dates
> > from and to params on the column IDDOCD. Then, if I am correct, the
> > reporting tool can accept this stored procedure via dates entered by the
> > user. I have not done SP in IBM i, so so far am bombing out. is there any
> > one can set me on the right path here?
> >
> >
> >
> > CREATE VIEW astccdta.acsusage
> > AS (
> > SELECT
> > IARCC9, IDDOCD, IDCOM#, ADSFX#, IDPRT#, IDGRC#, IDENT#, IDSFX#, IDPRLC,
> > IDNTU$, IDSHP#, ASTDTA.ADRESSAD.ADFNM, ASTDTA.ADRESSAD.ADLNM,
> > ASTTRN.OEINHDIH.IHVIAC, ASTDTA.ADRESSAD.ADSTTC, IDINV# FROM
> > ASTDTA.OEINDLID
> > INNER JOIN ASTDTA.ICPRTMIA ON ASTDTA.OEINDLID.IDPRT# =
> > ASTDTA.ICPRTMIA.IAPRT# INNER JOIN ASTTRN.OEINHDIH ON
> > ASTDTA.OEINDLID.IDIDC#=ASTTRN.OEINHDIH.IHIDC#
> > INNER JOIN ASTDTA.ADRESSAD ON
> > ASTDTA.ADRESSAD.ADENT#=ASTTRN.OEINHDIH.IHENT#
> > WHERE ASTDTA.ICPRTMIA.IARCC9='ACS'
> > AND ASTDTA.OEINDLID.IDCOM#='001' AND ASTDTA.OEINDLID.IDPRT# LIKE 'ACS%')
> > )
> > --
> > This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
> > list
> > To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe,
> > unsubscribe,
> > or change list options,
> > visit: http://lists.midrange.com/mailman/listinfo/midrange-l
> > or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take a
> > moment to review the archives at http://archive.midrange.com/midrange-l.
> >
> >
> > --
> > This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
> > list
> > To post a message email: MIDRANGE-L@xxxxxxxxxxxx
> > To subscribe, unsubscribe, or change list options,
> > visit: http://lists.midrange.com/mailman/listinfo/midrange-l
> > or email: MIDRANGE-L-request@xxxxxxxxxxxx
> > Before posting, please take a moment to review the archives
> > at http://archive.midrange.com/midrange-l.