Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

case when condition in where clause in oracle

user12251389Mar 14 2017 — edited Mar 14 2017

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;

This post has been answered by BluShadow on Mar 14 2017
Jump to Answer
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Apr 11 2017
Added on Mar 14 2017
7 comments
1,469 views