Embedded Select in Stored Procedure won't work!

[KellyStee]

I'm running just a simple query in a stored procedure and one of the fields in the 'Select' clause comes from an additional 'Select' statement (a.k.a. embedded 'Select'). The problem is that the stored procedure won't compile with the embedded select in the code. Is this right? Does anyone know of a way around this issue?

Thanks!!
Kelly
kelly.steensma@us.pwcglobal.com

 

[KellyStee]

By the way, I'm using Oracle 8i.

Kelly

 

[KellyStee]

This is my stored procedure code:

CREATE OR REPLACE PROCEDURE SP_KELLY
IS
BEGIN

INSERT INTO TBLKELLY(EMPLOYEE_NBR,
EMPLOYEE_COST)

SELECT E.EMPLOYEE_NBR,
(SELECT SUM(EC.EMPLOYEE_COST)
FROM DUAL
WHERE E.EMPLOYEE_ID = EC.EMPLOYEE_ID)
FROM EMPLOYEE E,
EMPLOYEE_COST EC;

COMMIT;

END;
/

And this is the error I get every time (it's highlights the second 'Select' statement when I get this message):
PLS-00103: Encountered the symbol "SELECT" when expecting one of the following:

( ) - + mod not null others <an identifier>


PLEASE HELP!!! It's driving me nuts!!!

 

[lewisp]

Try this:

CREATE OR REPLACE PROCEDURE SP_KELLY
IS
BEGIN

INSERT INTO TBLKELLY(EMPLOYEE_NBR,
EMPLOYEE_COST)
SELECT E.EMPLOYEE_NBR,
sum(ec.employee_cost)
FROM EMPLOYEE E,
EMPLOYEE_COST EC
where e.employee_id = ec.employee_id
group by e.employee_nbr;

COMMIT;

END;
/