Complex Procedure needs View!

From: MADEIRA SALLY <softsense_at_home.com>
Date: Wed, 23 Jun 1999 13:31:24 GMT
Message-ID: <3770E284.DDB4A6D5@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 - 08:31:24 CDT