I have below query where i am trying to use case when condition in where clause but its throwing an error from the first case when condition as 'ORA - 00905 missing keyword'.
create or replace procedure chk_kpi_reactivate_downtime as
begin
Select DISTINCT(KD.KPI_NAME),DS.TEMPLATE_NAME
from RATOR_MONITORING_CONFIGURATION.DOWNTIME_RESOURCE_CONFIG DR, RATOR_MONITORING_CONFIGURATION.KPI_DEFINITION KD --ON KD.SOAP_SERVICE_ID = DR.DOWNTIME_RESOURCE_ID
, RATOR_MONITORING_CONFIGURATION.DOWNTIME_CONFIG DS --ON DR.DOWNTIME_CONFIG_ID = DS.DOWNTIME_CONFIG_ID
, RATOR_MONITORING_CONFIGURATION.DOWNTIME_SCHEDULE DTS --ON DTS.DOWNTIME_CONFIG_ID = DS.DOWNTIME_CONFIG_ID
WHERE DTS.SCHEDULE_RUNNING = 'N' and KD.KPI_ACTIVE_DOWNTIME = 'N' --and DR.DOWNTIME_RESOURCE_TYPE = 'SOAP_SERVICE' and KD.SOAP_SERVICE_ID IS NOT NULL;
and DR.DOWNTIME_CONFIG_ID = DS.DOWNTIME_CONFIG_ID
and DTS.DOWNTIME_CONFIG_ID = DS.DOWNTIME_CONFIG_ID
and CASE WHEN (DR.DOWNTIME_RESOURCE_TYPE = 'SOAP_SERVICE') THEN KD.SOAP_SERVICE_ID = DR.DOWNTIME_RESOURCE_ID
WHEN (DR.DOWNTIME_RESOURCE_TYPE = 'SYSTEM_SQL_CHECK') THEN KD.SYSTEM_SQL_CHECK_ID = DR.DOWNTIME_RESOURCE_ID
WHEN (DR.DOWNTIME_RESOURCE_TYPE = 'ENGINE') THEN KD.ENGINE_ID = DR.DOWNTIME_RESOURCE_ID
WHEN (DR.DOWNTIME_RESOURCE_TYPE = 'FTP_SFTP_SERVER') THEN KD.FTP_SFTP_SERVER_ID = DR.DOWNTIME_RESOURCE_ID
WHEN (DR.DOWNTIME_RESOURCE_TYPE = 'DB_CONNECTION') THEN KD.DB_CONNECTION_ID = DR.DOWNTIME_RESOURCE_ID
END;
end chk_kpi_reactivate_downtime;