Re: View in a procedure.

Hello,
I see that your view is very simple. So the problem is perhaps you don't have indexes ?. Another point is that select distinct proceeds to a sort of your result and if the result is big it may be slow to return you.
Cheers

"Stéphane D'Hoedt" wrote:
>
> Hi,
>
> I wrote a view like this:
>
> CREATE OR REPLACE VIEW QREKSE ( REK_ID,
> R_SA_ID, R_SE_ID, SE_NAAM, R_NAAMSE,
> R_VOLGNUMMERSE, R_REFKLIENT, R_FAKNUMMER, R_REDEN,
> R_DATUMSCHULD, R_BEDRAGSCHULD, R_INPUTDATUM, HSSE,
> MIR, AMR, TIR, SCHADE,
> TOTEXCL, R_WN, SE_ID, R_REKOPGELOST,
> R_REKAFGEREKEND, SE_STRAAT, SE_HUISNR, SE_POSTNR,
> SE_GEMEENTE, SE_DOS, SE_AFRHSPERC, SE_AFRHS,
> SE_AFRCOM ) AS SELECT DISTINCT REKENING.REK_ID, REKENING.R_SA_ID,
> REKENING.R_SE_ID,
> SCHULDEISER.SE_NAAM, REKENING.R_NAAMSE, REKENING.R_VOLGNUMMERSE,
> REKENING.R_REFKLIENT, REKENING.R_FAKNUMMER, REKENING.R_REDEN,
> REKENING.R_DATUMSCHULD,
> REKENING.R_BEDRAGSCHULD, REKENING.R_INPUTDATUM,
> (ROUND(R_BEDRAGSCHULD/SE_VERHOGINGSVPERC)+SE_VERHOGINGSV) AS HSSE,
> ROUND(R_BEDRAGSCHULD*SE_INTREST) AS MIR,
> TRUNC(MONTHS_BETWEEN( NVL(R_REKOPGELOST,SYSDATE), R_DATUMSCHULD)) AS
> AMR,
>
> OUND(R_BEDRAGSCHULD*SE_INTREST)*TRUNC(MONTHS_BETWEEN( NVL(R_REKOPGELOST,SYSD
> ATE), R_DATUMSCHULD)) AS TIR,
> GREATEST(ROUND(R_BEDRAGSCHULD*SE_SCHADELOOSSTELLING),SE_MINIMUM) AS
> SCHADE,
>
> (ROUND(R_BEDRAGSCHULD/SE_VERHOGINGSVPERC)+SE_VERHOGINGSV)+ROUND(R_BEDRAGSCHU
> LD*SE_INTREST)*TRUNC(MONTHS_BETWEEN( NVL(R_REKOPGELOST,SYSDATE),
> R_DATUMSCHULD))+GREATEST(ROUND(R_BEDRAGSCHULD*SE_SCHADELOOSSTELLING),SE_MINI
> MUM) AS TOTEXCL,
> REKENING.R_WN, SCHULDEISER.SE_ID, REKENING.R_REKOPGELOST,
> REKENING.R_REKAFGEREKEND,
> SCHULDEISER.SE_STRAAT, SCHULDEISER.SE_HUISNR, SCHULDEISER.SE_POSTNR,
> SCHULDEISER.SE_GEMEENTE, SCHULDEISER.SE_DOS,
> SCHULDEISER.SE_AFRHSPERC,
> SCHULDEISER.SE_AFRHS, SCHULDEISER.SE_AFRCOM
> FROM SCHULDEISER, REKENING
> WHERE SCHULDEISER.SE_ID = REKENING.R_SE_ID AND
> (((REKENING.REK_ID) IS NOT NULL))
>
> And the view is to slow to run it. So I will write it in a procedure,
> because I know it is much faster to run it.
> But I have a problem. I have some problem to write the procedure. Can
> someone help me how to write this view in a procedure.
>
> Thanks,
> Stéphane D'Hoedt
Received on Mon Dec 20 1999 - 11:07:47 CET