Hello,

I have the following views and queries. What code can I use to incorporate the the SELECT SUM statements into the campus_3rdqtrcontr view? Currently I am keying in the actual values (1025 & 810) in this statement ROUND(CAMPUSTARGETS.QTR3/1025,2)*810 "CONTRIBUTION 3RD QTR". I would just like to reference the SELECT SUM statements for these values to save some time if possible. Ive tried but to no avail.

- Thanks for your help.


SQL> CREATE VIEW CAMPUS_TARGETS AS
2 SELECT CAMPUSTARGETS.CAMPUSN, CAMPUSTARGETS.QTR3
3 FROM CAMPUSTARGETS;

View created.

SQL> SELECT * FROM CAMPUS_TARGETS;

CAMPUS QTR3
------ ---------
Main 500
East 250
West 125
North 150

SELECT SUM(QTR3)
FROM CAMPUSTARGETS;

SUM(QTR3)
---------
1025

SQL> SELECT SUM(AMOUNT)
2 FROM CONTRIBUTION
3 WHERE CDATE > TO_DATE('80-06-03', 'YY-MM-DD')
4 AND CDATE < TO_DATE('80-10-03', 'YY-MM-DD');

SUM(AMOUNT)
-----------
810

SQL> CREATE VIEW CAMPUS_3RDQTRCONTR AS
2 SELECT CAMPUSTARGETS.CAMPUSN,
3 ROUND(CAMPUSTARGETS.QTR3/1025,2)*810 "CONTRIBUTION 3RD QTR"
4 FROM CAMPUSTARGETS;

View created.

SQL> SELECT * FROM CAMPUS_3RDQTRCONTR;

CAMPUS CONTRIBUTION 3RD QTR
------ --------------------
Main 396.9
East 194.4
West 97.2
North 121.5

You could use 2 "dummy" inner joins in your view. The following code is just out of my head, as I don't have access to my computer right now. Should be something like this :

CREATE VIEW CAMPUS_3RDQTRCONTR AS
SELECT CAMPUSTARGETS.CAMPUSN,
ROUND(CAMPUSTARGETS.QTR3/SUM_QTR3_CAMPUSTARGETS.QTR3)*SUM_QTR3_CONTRIBUTION S.QTR3 "CONTRIBUTION 3RD QTR"
FROM CAMPUSTARGETS
/* FIRST DUMMY INNER JOIN, ALWAYS RETURNS ONE, AND ONLY ONE ROW */
INNER JOIN
(SELECT SUM(QTR3) AS QTR3
FROM CAMPUSTARGETS) SUM_QTR3_CAMPUSTARGETS ON 1 = 1
/* SECOND DUMMY INNER JOIN ON CONTRIBUTION */
INNER JOIN
(SELECT SUM(AMOUNT) AS QTR3
FROM CONTRIBUTION
WHERE CDATE > TO_DATE('80-06-03', 'YY-MM-DD')
AND CDATE < TO_DATE('80-10-03', 'YY-MM-DD')) SUM_QTR3_CONTRIBUTIONS ON 1 = 1
;