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?
By the way, I'm using Oracle 8i.

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

 

[Sonix]

Can you show us the SQL, maybe theres some brackets missing?

 

[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!!!

 

[carp]

You can't stick a SELECT statement where a column name is supposed to be.

Try this instead:

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;

 

[KellyStee]

Thanks for the help, carp!

If I run my entire select statment outside of the stored procedure it works, but it doesn't work inside the stored procedure. Why is that?

If the 'Where' clause in my embedded 'Select' ever became more complex (which it is for other stored procedures), the solution becomes much more complex. So coding &quot;around&quot; the embedded select is much harder. I this the only way?

Kelly