Sandeep_Patil Sandeep_Patil - 1 year ago 74
SQL Question

sql query to run based on a column value

I have a table with below values for database : db1

database sync_status validation remark
db1 successful failed error
db1 successful successful all is well

I don't want the query to return any row, if the validation is successful for database db1. But in my case it is returning the row with failed validation.

select database,sync_status,validation,remark from bcv_sync where database='db1' and validation!='successful' and validation='failed' and rownum = 1;

But if in case the db1 validation is failed the query must return the row with failed validation.

Answer Source

If I interpret your cryptic question then you want to show the rows that failed only if there is not a row that succeeded:

SELECT database,
FROM   (
  SELECT d.*,
         COUNT( CASE validation WHEN 'successful' THEN 1 END ) OVER ()
           AS num_success
  FROM   db1 d
WHERE  validation = 'failed'
AND    num_success = 0;
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download