PL/SQL (MOSC)

MOSC Banner

Oracle database pl sql syntax error

edited Dec 18, 2024 4:57PM in PL/SQL (MOSC) 5 commentsAnswered

I am trying to create code that will allow user to enter a QUEUENAME. Then if rows are returned in the select statement enter a record with the results. If there is no records returned I want a record inserted with norows information. When I run the case in Toad there is a syntax error where I have THEN. Can someone help me resolve this syntax error?

SELECT CASE
WHEN EXISTS
(SELECT A.QUEUENAME, DECODE(A.SUBCONSTATUS, 0, 'ERROR', 1, 'NEW', 2, 'STARTED', 3, 'WORKING', 4, 'DONE', 5, 'RETRY', 6, 'TIMEOUT', 7, 'EDITED', 8, 'CANCELED', 9, 'HOLD', A.SUBCONSTATUS) STATUS, TRUNC(LASTUPDDTTM),COUNT() FROM SYSADM.PSAPMSGSUBCON A where A.SUBCONSTATUS in (0,1,2,4) AND A.QUEUENAME = '&QUEUENAME' and TRUNC(LASTUPDDTTM) = TRUNC(SYSDATE) GROUP BY A.QUEUENAME, DECODE(A.SUBCONSTATUS, 0, 'ERROR', 1, 'NEW', 2, 'STARTED', 3, 'WORKING', 4, 'DONE', 5, 'RETRY', 6, 'TIMEOUT', 7, 'EDITED', 8, 'CANCELED', 9, 'HOLD', A.SUBCONSTATUS), TRUNC(LASTUPDDTTM)

Tagged:

Howdy, Stranger!

Log In

To view full details, sign in to My Oracle Support Community.

Register

Don't have a My Oracle Support Community account? Click here to get started.

Category Leaderboard

Top contributors this month

New to My Oracle Support Community? Visit our Welcome Center

MOSC Help Center