Creating simple select statement with stored procedure

[kvang]

Getting error message "PLS-00428: an INTO clause is expected in this SELECT statement" with the following code:

CREATE OR REPLACE PROCEDURE Sp_Select_Avgtimecompleted 
IS
BEGIN
	SELECT T_WORKTYPE, COUNT(*) AS ITEM_COUNT, TRUNC(AVG(F_TIMESTAMP - F_STARTTIME))+1 AS AVG_DAYS
	FROM REPORT.BK_WOBCOMPLETEDQUEUE
	WHERE TRUNC(F_TIMESTAMP) >= (TRUNC(SYSDATE) - 7)
	AND TRUNC(F_TIMESTAMP) < TRUNC(SYSDATE)
	GROUP BY T_WORKTYPE;
END;

Help! See anything I'm missing?

 

[BJCooperIT]

The error means just that, you must place your selected fields INTO declared variables.

CREATE OR REPLACE PROCEDURE sp_select_avgtimecompleted 
IS
    v_worktype  report.bk_wobcompletedqueue.t_worktype%TYPE;
    v_count     NUMBER;
    v_average   NUMBER;
BEGIN
    SELECT t_worktype,
           COUNT(*) AS item_count,
           trunc(AVG(f_timestamp - f_starttime)) + 1 AS avg_days
    INTO   v_worktype,
           v_count   
           v_average 
    FROM   report.bk_wobcompletedqueue
    WHERE  trunc(f_timestamp) >= (trunc(SYSDATE) - 7)
    AND    trunc(f_timestamp) < trunc(SYSDATE)
    GROUP  BY t_worktype;
END;

^{Beware of false knowledge; it is more dangerous than ignorance. ~George Bernard Shaw}
Consultant Developer/Analyst Oracle, Forms, Reports & PL/SQL (Windows)
My website: www.EmuProductsPlus.com

 

[BJCooperIT]

Sorry, hit post too soon.

You need a comma after v_count in the INTO list. The aliases are unnecessary. If this returns more than one row, and it will if there is more than one work type, this select will need to be processed in a cursor or you will encounter a TOO_MANY_ROWS exception.

 

[kvang]

Thanks for the tip. The procedure compiled fine, but it bugs out when I execute it. It will return more than one row.

 

[SantaMufasa]

BKou,

The problem is that in PL/SQL, a "SELECT...INTO..." MUST return exactly one row...no more, no less. If you have more than one row that returns from a query (as you have with this one), then I recommend a CURSOR FOR LOOP:

CREATE OR REPLACE PROCEDURE Sp_Select_Avgtimecompleted 
IS
BEGIN
    for x in (SELECT T_WORKTYPE
                    ,COUNT(*) AS ITEM_COUNT
                    ,TRUNC(AVG(F_TIMESTAMP - F_STARTTIME))+1 AS AVG_DAYS
                FROM REPORT.BK_WOBCOMPLETEDQUEUE
               WHERE TRUNC(F_TIMESTAMP) >= (TRUNC(SYSDATE) - 7)
                 AND TRUNC(F_TIMESTAMP) < TRUNC(SYSDATE)
               GROUP BY T_WORKTYPE) LOOP
        dbms_output.put_line
            (x.T_WORKTYPE||', '||x.ITEM_COUNT||', '||x.AVG_DAYS);
    end loop;
END;
/

I invoked the "dbms_output.put_line" procedure simply to illustrate how to refer to the expressions returning from the CURSOR FOR LOOP.

Let us know if this helps to resolve your need.


Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]