CODEWITHSUNDEEP
sqloracle-databaseboolean-logic
pwlm
pwlm

Reputation: 184

Oracle SQL/PLSQL: Return Boolean value when multiple criterion met across many rows of data

I have a query that currently returns multiple rows of data. I need to analyse this data using PL/SQL and/or SQL to determine whether it meets the business requirements - returning either TRUE or FALSE.

Sample Data 1                 Sample Data 2               Sample Data 3

TYPE        STATUS            TYPE        STATUS          TYPE        STATUS  
Red         Open              Red         Open            Red         Open
Blue        Open              Blue        Open            Blue        Open
Yellow      Open              Yellow      Open            Yellow      Open
Red         Closed            Yellow      Closed
Red         Pending

Requirements - if met return TRUE, otherwise FALSE:

The STATUS for each TYPE must be 'Open'.

If there is more than 1 record for the same TYPE then the STATUS on at least 1 record must be 'Open', the STATUS on the remaining records must be 'Closed'.

Results based on requirements above:

Sample Data 1 = FALSE
Sample Data 2 = TRUE
Sample Data 3 = TRUE

Any and all help much appreciated, apologies if this question is a duplicate.

Upvotes: 0

Views: 451

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269953

Oracle SQL doesn't have boolean types, so let's use 0 and 1:

select type,
       (case when count(distinct case when status = 'open' then type end) = count(distinct type) and 
                  count(case when status = 'open' then type end) = count(distinct type) and
                  sum(case when status not in ('open', 'closed') then 1 else 0 end) = 0                 
             then 1 else 0
        end) as flag
from t
group by type;

The logic:

  • The first condition says that each type has at least one "open".
  • The second condition (combined with the first) says that each type has exactly one "open".
  • The third condition says that the only status are "open" and "closed".

Upvotes: 1

Related Questions