Re: Complex Procedure needs View!

Hi Sally,

Why do you want to drop the views once they are created?

Since views are virtual entities that aren't populated until a query that uses them is executed it is probably cheaper, in terms of system resources, to define the view once and use it repeatedly rather than define it every time it is used.

regards
Jerry Gitomer

MADEIRA SALLY wrote in message <3770E284.DDB4A6D5_at_home.com>...
>I have some very complex SQL statements within a procedures that
>will be used to populate a Crystal report. I have had some great
>input but here is another guestion about VIEWS.
>
>Becuase my SQL Clause are very complex, I would like to
>do a CREATE VIEW .... FROM Select.... and then use
>that as my result to create another resultset...
>Then DROP VIEW ....
>
>Problem, I am doing this in a procedure and I keep getting
>an error on the CREATE... Am I setting up my procedure wrong.
>
>See Below:
>***************************************************
>PROCEDURE ABLMACC1.R10_ParticipationListing
>(AsOfDate IN DATE,
> FinalRS_cv IN OUT Cursor_Types.FinalRS_Cur)
>
>IS
>
>/*Get All Participation Records of LoanType = "PT" and PartType <> "IL
>(Interoffice Particpations) */
>/*Get Loan Balances where LoanType not in "LC", "BA", "PT" */
>
>CREATE VIEW Proc_vw AS
>
>SELECT Comdyear.OfficeNum,
> Comdyear.CliNum,
> Comdyear.LoanNum,
> SUM(Get_NonInteroffice_LoanBalance(Comdyear.LoanBal, Comdlon2.PTType,
>Comd02a.LoanType)) EOMBalance,
> SUM(Get_NonParticipant_LoanBalance(Comdyear.LoanBal, Comd02a.LoanType))
>GrossBalance,
> Comd02a.LoanName
>
>FROM Comdyear,Comd02a,Comdlon2
>
>WHERE (Comdyear.LoanNum=Comd02a.LoanNum
> AND Comdyear.Clinum=Comd02a.Clinum
> AND Comdyear.OfficeNum=Comd02a.OfficeNum)
> AND (Comd02a.LoanNum = Comdlon2.LoanNum
> AND Comd02a.CliNum=Comdlon2.CliNum
> AND Comd02a.OfficeNum=Comdlon2.OfficeNum)
> AND (Comdyear.Date_R BETWEEN
>TO_DATE('01'||TO_CHAR(AsOfDate,'MMYYYY'),'DDMMYYYY') AND
>LAST_DAY(AsOfDAte))
>GROUP BY Comdyear.OfficeNum, Comd02a.LoanName, Comdyear.CliNum,
>Comdyear.LoanNum
>
>GROUP BY S.OfficeNum, S.LoanName;
>
>BEGIN
>
> OPEN FinalRS_cv FOR
> SELECT * FROM Proc_vw WHERE ........
>END;
>
>*********************************************
>
>I simplified the procedure bu there would actually be two more SELECT
>statements
>that are very complex.... To bad I just couldn't reference a cursor
>within a
>select statement!!!!
>
>Thanks in advance
>Sally
Received on Wed Jun 23 1999 - 12:24:12 CDT