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.
(SELECT TBL1.CLIENT_CODE_ID AS CLIENTCODE, count(*) AS TaskCount FROM EP3R.EP_CLIENT_CODE TBL1,
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,
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;
Any help will be greatly appreciated.
You need to select into some variables e.g.
into v_client_name, v_taskcount, v_process_count
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.
Click Here to Expand Forum to Full Width