sqlpostgresqlboolean-logic

Compare 3 CASE expressions for an overall value

I have a Postgres database with a table named servers containing these columns:

Some of the compliance values aren't as black and white as 'PASS' or 'FAIL'. So I first generate com_passfail with just 'PASS' / 'FAIL' using a CASE expression.
Some of the os versions aren't black and white either. So I generate os_passfail with just 'PASS' / 'FAIL'. Finally, I generate overall_status like this:

If com_passfail or os_passfail is 'PASS' then a new column overall_status is 'PASS'.
If com_passfail or os_passfail is 'FAIL' and ref_id is NOT NULL then overall_status is 'PASS'.
If com_passfail or os_passfail is 'FAIL' and ref_id is NULL then overall_status is 'FAIL'.

Sample rows for table servers:

server compliance os ref_id
SERVR1 PASS OK
SERVR2 GOOD BAD
SERVR3 OK VALID
SERVR4 FAIL PASS
SERVR5 BAD ACCEPT TICKET1
SERVR6 NOGOOD FAIL TICKET2

Expected Output:

server compliance os ref_id com_passfail os_passfail overall_status
SERVR1 PASS OK PASS PASS PASS
SERVR2 GOOD BAD PASS FAIL FAIL
SERVR3 OK VALID PASS PASS PASS
SERVR4 FAIL PASS FAIL PASS FAIL
SERVR5 BAD ACCEPT TICKET1 FAIL PASS PASS
SERVR6 NOGOOD FAIL TICKET2 FAIL FAIL PASS

I tried CASE expressions to implement above rules:

SELECT *,
CASE
WHEN compliance = 'PASS' THEN 'PASS'
WHEN compliance = 'GOOD' THEN 'PASS'
WHEN compliance = 'OK' THEN 'PASS'
WHEN compliance = 'FAIL' THEN 'FAIL'
WHEN compliance = 'BAD' THEN 'FAIL'
WHEN compliance = 'NOGOOD' THEN 'FAIL'
END AS com_passfail,
CASE
WHEN os = 'PASS' THEN 'PASS'
WHEN os = 'OK' THEN 'PASS'
WHEN os = 'VALID' THEN 'PASS'
WHEN os = 'ACCEPT' THEN 'PASS'
WHEN os = 'FAIL' THEN 'FAIL'
WHEN os = 'BAD' THEN 'FAIL'
END AS os_passfail
FROM servers

I created a view using the above query and another view selecting everything from the query and then doing a comparison between com_passfail and os_passfail to determine the overall_status value.

Is there a way to generate the result table above in a single query?

Solution

  • Unless there are undeclared corner cases, simplify with Boolean logic instead of CASE expressions.
    And one plain subquery (even that optional) instead of multiple views:

    SELECT *, com_pass AND os_pass OR ref_id IS NOT NULL AS overall_pass
FROM  (
   SELECT *
        , compliance IN ('GOOD', 'OK', 'PASS') AS com_pass
        , os = ANY ('{PASS,OK,VALID,ACCEPT}') AS os_pass
   FROM   servers
   ) sub;

    Demonstrating equivalent IN and ANY expressions. Either is good. See:

    com_pass AND os_pass OR ref_id IS NOT NULL works without parentheses because of operator precedence.

    If you need text output:

    SELECT *, CASE WHEN com_pass AND os_pass OR ref_id IS NOT NULL
               THEN 'PASS' ELSE 'FAIL' END AS overall_pass
FROM  (...) sub;  -- like above

    fiddle

    Produces your expected result precisely.

    Aside: use legal, lower-case, unquoted identifiers in Postgres if at all possible. See: