-
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
-
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.
-
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.
-
Thanks for all your help.
J
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|