View in a procedure.

From: Stéphane D'Hoedt <sdhoedt_at_softhome.net>
Date: Mon, 20 Dec 1999 10:24:36 +0100
Message-ID: <83ksfs$plq$1@naxos.belnet.be>

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 - 03:24:36 CST