CODEWITHSUNDEEP
sqloracle-database
anand
anand

Reputation: 47

Using where condition for CASE statement

I have a SQL statement of the below format:

SELECT 
    p.env, p.name, i.sys_name, i.hostname,
    CASE
       WHEN (COUNT(DISTINCT p.env) OVER (PARTITION BY i.sys_name) +
             COUNT(DISTINCT i.sys_name) OVER (PARTITION BY i.sys_name)) = 2
          THEN 'no issues'
          ELSE 'has issues'
    END AS result
FROM 
    instances i 
JOIN 
    properties p ON i.target_name = p.target_name

How can I add a where condition such that I can get only the rows with result as 'has issues'.

Thanks.

Upvotes: 0

Views: 68

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269763

One obvious solution is a subquery:

SELECT *
FROM (SELECT p.env, p.name, i.sys_name, i.hostname,
             (CASE WHEN (COUNT(DISTINCT p.env) OVER (PARTITION BY i.sys_name) +
                         COUNT(DISTINCT i.sys_name) OVER (PARTITION BY i.sys_name)
                        ) = 2
                  THEN 'no issues' ELSE 'has issues'
               END) AS result
      FROM instances i JOIN
           properties p
           ON i.target_name = p.target_name
     ) ip
WHERE result = 'has issues';

Upvotes: 2

Related Questions