I am trying to use a CASE statement in my WHERE clause:
SELECT T.TABLE_NAME,
SUBSTR(O.OBJECT_TYPE,1,2) "TY",
T.COLUMN_NAME,
T.DATA_TYPE,
T.DATA_LENGTH "DATA LNG",
T.COLUMN_ID "COL ID"
FROM ALL_TAB_COLUMNS "T",
ALL_OBJECTS "O"
WHERE T.TABLE_NAME = O.OBJECT_NAME AND
T.OWNER = UPPER('BT') AND
T.COLUMN_NAME LIKE UPPER('%&&PRT%') AND
(CASE
WHEN &OTY = 'A' THEN
O.OBJECT_TYPE IN ('TABLE', 'VIEW', 'PACKAGE', 'PACKAGE BODY','PROCEDURE','FUNCTION', 'SEQUENCE', 'TRIGGER')
WHEN &OTY = 'T' THEN
O.OBJECT_TYPE = 'TABLE'
END) = 1
I get the following error:
O.OBJECT_TYPE IN ('TABLE', 'VIEW', 'PACKAGE', 'PACKAGE BODY','PROCEDURE','FUNCTION', 'SEQUENCE', 'TRIGGER')
*
ERROR at line 14:
ORA-00905: missing keyword
----------------------
I can't figure out this error and any documentation or other examples have not helped.
Any suggestions would be greatly appreciated.