Is the following code valid? I am getting errors.

Question

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:

"I'm getting errors. Please help!" is like saying "My car doesn't work. How can I fix it?" You need to provide more info if you want help. Start with specific error messages. — Ken White

OMG Ponies OMG Ponies · Accepted Answer · 2011-03-23T03:38:31

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.

Is the following code valid? I am getting errors.

2 Answers