CREATE OR REPLACE VIEW POINTS AS
DECLARE
avgDurationOurFault number(5);
avgDurationCustomersFault number(5);
avgDuration number(5);
BEGIN
(select ceil(avg(abs(total_time))) into avgDuration from inquiry);
select ceil(avg(total_duration)) into avgDurationOurFault
from
(
select customer_no, sum(abs(total_time)) total_duration
from inquiry
where cat_id in ('C900', 'C901', 'C902', 'C905', 'C907', 'C908', 'C909')
GROUP BY customer_no);
select ceil(avg(total_duration)) into avgDurationCustomersFault
from
(
select customer_no, sum(abs(total_time)) total_duration
from inquiry
where cat_id in ('C903','C904', 'C906')
group by customer_no);
select t1.customer_no, t1.callPoints, t1.durationPoints, t2.catgPoints,
t1.callPoints+t1.durationPoints+t2.catgPoints as totalPoints
from
(
select customer_no, count(inquiry_id)*avgDuration callPoints , sum(abs(total_time)) durationPoints
from inquiry
group by customer_no
) t1
inner join (
select customer_no, sum(points) catgPoints
from
(
select customer_no,
case
when cat_id in ('C903','C904', 'C906')
then 0
when cat_id in ('C900', 'C901', 'C902', 'C905', 'C907', 'C908', 'C909')
then 2*avgDuration + abs(avgDurationCustomersFault - avgDurationOurFault)
else
0
end as points
from inquiry
)
group by customer_no
) t2
on t1.customer_no = t2.customer_no;
END;
/
--------------------ERRORS BELOW---------------------------------------------------
Error starting at line 1 in command: CREATE OR REPLACE VIEW POINTS AS DECLARE avgDurationOurFault number(5) Error at Command Line:1 Column:32 Error report: SQL Error: ORA-00928: missing SELECT keyword 00928. 00000 - "missing SELECT keyword" *Cause:
*Action:Error starting at line 4 in command: avgDurationCustomersFault number(5) Error report: Unknown Command
Error starting at line 5 in command: avgDuration number(5) Error report: Unknown Command
Error starting at line 7 in command:
BEGIN
(select ceil(avg(abs(total_time))) into avgDuration from inquiry);
select ceil(avg(total_duration)) into avgDurationOurFault
from
(
select customer_no, sum(abs(total_time)) total_duration
from inquiry
where cat_id in ('C900', 'C901', 'C902', 'C905', 'C907', 'C908', 'C909')
GROUP BY customer_no);
select ceil(avg(total_duration)) into avgDurationCustomersFault
from
(
select customer_no, sum(abs(total_time)) total_duration
from inquiry
where cat_id in ('C903','C904', 'C906')
group by customer_no);
select t1.customer_no, t1.callPoints, t1.durationPoints, t2.catgPoints,
t1.callPoints+t1.durationPoints+t2.catgPoints as totalPoints
from
(
select customer_no, count(inquiry_id)*avgDuration callPoints , sum(abs(total_time)) durationPoints
from inquiry
group by customer_no
) t1
inner join (
select customer_no, sum(points) catgPoints
from
(
select customer_no,
case
when cat_id in ('C903','C904', 'C906')
then 0
when cat_id in ('C900', 'C901', 'C902', 'C905', 'C907', 'C908', 'C909')
then 2*avgDuration + abs(avgDurationCustomersFault - avgDurationOurFault)
else
0
end as points
from inquiry
)
group by customer_no
) t2
on t1.customer_no = t2.customer_no;
END;
Error report: ORA-06550: line 3, column 2: PLS-00103: Encountered the symbol "(" when expecting one of the following:
begin case declare exit for goto if loop mod null pragma raise return select update while with << close current delete fetch lock insert open rollback savepoint set sql execute commit forall merge pipe The symbol "update" was substituted for "(" to continue. ORA-06550: line 3, column 37: PLS-00103: Encountered the symbol "INTO" when expecting one of the following:
. ( , * % & - + / at mod rem as from || The symbol ". was inserted before "I ORA-06550: line 3, column 67: PLS-00103: Encountered the symbol ";" when expecting one of the following:
set ORA-06550: line 30, column 3: PLS-00103: Encountered the symbol "INNER" when expecting one of the following:
, ; for group having intersect minus order start union where
connect 06550. 00000 - "line %s, column %s:\n%s" *Cause: Usually a PL/SQL compilation error. *Action:
Use:
CREATE OR REPLACE VIEW POINTS AS
SELECT a.customer_no,
a.callPoints,
a.durationPoints,
a.catgPoints,
a.callPoints + a.durationPoints + a.catgPoints as totalPoints
FROM (SELECT i.customer_no,
COUNT(i.inquiry_id) * x.avgDuration AS callPoints,
SUM(ABS(i.total_time)) durationPoints,
SUM(CASE
WHEN i.cat_id IN ('C900', 'C901', 'C902', 'C905', 'C907', 'C908', 'C909') THEN
2 * x.avgDuration + ABS(z.avgDurationCustomersFault - y.avgDurationOurFault)
ELSE 0
END) AS catgpoints
FROM INQUIRY i
CROSS JOIN (SELECT CEIL(AVG(ABS(t.total_time))) AS avgDuration
FROM INQUIRY t) x
CROSS JOIN (SELECT CEIL(AVG(total_duration)) AS avgDurationOurFault
FROM (SELECT SUM(ABS(t.total_time)) AS total_duration
FROM INQUIRY t
WHERE t.cat_id IN ('C900', 'C901', 'C902', 'C905', 'C907', 'C908', 'C909')
GROUP BY t.customer_no) y
CROSS JOIN (SELECT CEIL(AVG(total_duration)) AS avgDurationCustomersFault
FROM (SELECT SUM(ABS(t.total_time)) AS total_duration
FROM INQUIRY
WHERE t.cat_id IN ('C903','C904', 'C906')
GROUP BY t.customer_no) z
GROUP BY i.customer_no) a
It is possible to combine "y" and "z", by using a CASE statement to sum values according to the cat_id. Someone else can golf with it.
The problem with your query is that you were trying to use multiple, unrelated SELECT statements. A view is a single SELECT statement - you can use subqueries, derived tables/inline views, etc but they have to be inside the single query like you see in my example. What you posted is more like what you'd find in a stored procedure or function. You can't use variables like how you were attempting, and you didn't need to -- just needed a CROSS JOIN.
Subquery factoring (AKA WITH clause, CTE) is possible to use, but there's generally little to no performance benefit.