Stored Procedure - Syntax

[joyce_ank]

One of the developers has written a sql script which he want to run in Oracle. This has to be in PL/SQL syntax. Currently when this code is run it comes back as invalid.

Could someone tell me what's wrong this code.

BEGIN

SELECT CODE.CLIENT_CODE_ID,
CLIENT.NAME,
TaskCount,
ProcessCount
FROM
EP3R.EP_CLIENT_CODE CODE,
EP3R.EP_CLIENT_NAME CLIENT,
(SELECT TBL1.CLIENT_CODE_ID AS CLIENTCODE, count(*) AS TaskCount FROM EP3R.EP_CLIENT_CODE TBL1,
WORKFLOW3R.WF2_TASK_ENTITY_VALUES TBL4,
WORKFLOW3R.WF2_TASK_INSTANCE TBL5
WHERE TBL1.ClIENT_CODE_ID = (TO_NUMBER(TBL4.TEV_VALUE))
AND TBL4.TEV_TASK_INSTANCE_ID = TBL5.TI_ID
AND TBL4.TEV_ENTITY_ID = 99
GROUP BY TBL1.CLIENT_CODE_ID) PART1,
(SELECT TBL6.CLIENT_CODE_ID AS CLIENTCODE2, count(*) AS ProcessCount from EP3R.EP_CLIENT_CODE TBL6,
WORKFLOW3R.WF2_COLLECTION_ENTITY_VALUES TBL2,
WORKFLOW3R.WF2_COLLECTION_INSTANCE TBL3
WHERE TBL6.CLIENT_CODE_ID = (TO_NUMBER(TBL2.CEV_VALUE))
AND TBL2.CEV_COLLECTION_INSTANCE_ID = TBL3.CI_ID
AND TBL2.CEV_ENTITY_ID = 99
GROUP BY TBL6.CLIENT_CODE_ID) PART2;

END IF;


Any help will be greatly appreciated.

Thanks

J

[Scorby]

You need to select into some variables e.g.

SELECT CODE.CLIENT_CODE_ID,
CLIENT.NAME,
TaskCount,
ProcessCount
into v_client_name, v_taskcount, v_process_count
from ...

However, if you get back more than one row it will raise an error. You will need to rewrite it as a cursor in that case.

I suggest you spend some time reading the PL/SQL manual as you obviously don't know much about the language.

[simflex]

first, i will start copy and paste each segment of the code and test them seperately.
second, you have a begin but no end.
third, you have an ending end if without a corresponding beginning if statement.
fourth, you cannot run this query in pl/sql as it is unless of course you use a cursor.
I am sure there are more problems with it but those stand out.

[joyce_ank]

Thanks for all your help.

J