If then else case PL/SQL if text is in list

Question

I have a column that will return something like this:

01
05
Z7, Z4, 01
09,08

I then have two static lists: list a = [01, 02, 03, 04] list b = [Z7, Z2, 05]

and our case statement returns one of the following:

group a
group b
mixed
none

I need to write a CASE statement to first breakdown each row using a comma delimiter, then loop through each value to see if the codes are part of list a exclusively, list b exclusively, a mix of both, or neither.

To put this into practice, we'd look at the first item, 01.

It exists in list a, but not in list b, so the case would return group a

The second result, 05, exists in list b, but not in list a, so it would return group b.

The third result, Z7, Z2, 01 contains multiple codes. Because at least ONE of these codes is in list b, and at least ONE of these codes exists in list a, we the case would return mixed. The fact that it also contains a code that doesn't fit into either group doesn't matter because at least one code exists in each list.

The fourth result, 09,08 would return none because neither 09 nor 08 exist in either list.

Is this possible to do in PL/SQL?

• Paul Vernon: Which DBMS are you using? You have tagged both Orace and Db2. Also what have you tried so far? DDL always helps too
• Brian Powell: My mistake - I didn't mean to tag db2 - I'm using Oracle, standard PL/SQL.
• Colin 't Hart: When you say a list, I presume you mean a comma-delimited string?

Answer 1

For starters, you could do the following: stick all list items - together with the group names - into a table (Oracle 12.1):

create table grouplists ( item_, group_ )
as
select '01', 'a' from dual union all
select '02', 'a' from dual union all
select '03', 'a' from dual union all
select '04', 'a' from dual union all
select 'Z7', 'b' from dual union all
select 'Z2', 'b' from dual union all
select '05', 'b' from dual ;

You already have the table containing the samples. For testing here:

create table samples ( value_ )
as 
select '01' from dual union all
select '05' from dual union all
select 'Z7, Z4, 01' from dual union all
select '09, 08' from dual ;

The following function will accept a "sample" ie a comma-separated value, which gets split up into "items". Each item is used in a SELECT ... INTO ... statement, picking up a "count" from the GROUPLISTS table. The function returns a string, containing the group membership ie (group a, group b, mixed, or none). The only aspect that is (maybe) not straightforward (in this function's code) is the recursive SELECT ... CONNECT BY. There are examples of this on SO eg this one.

create or replace function findgroup ( 
 sample varchar2 
) return varchar2
is
 ila pls_integer := 0 ; -- increase if item is found in list a
 ilb pls_integer := 0 ; -- increase if item is found in list b
 ccount pls_integer := 0 ; -- intermediate count
 retval varchar2( 4000 ) := '' ;
begin
 for s in ( 
 select unique -- split the sample into items
 trim( regexp_substr( sample, '[^,]+', 1, level ) ) item_
 from ( select sample from dual ) 
 connect by instr( sample, ',', 1, level - 1 ) > 0
 ) loop
--
 select count(*) into ccount 
 from grouplists where item_ = s.item_ and group_ = 'a' ;
 ila := ila + ccount ;
--
 select count(*) into ccount
 from grouplists where item_ = s.item_ and group_ = 'b' ;
 ilb := ilb + ccount ; 
-- 
 end loop; 
 retval := case 
 when ila > 0 and ilb > 0 then 'mixed'
 when ila > 0 and ilb = 0 then 'group a'
 when ilb > 0 and ila = 0 then 'group b'
 else 'none'
 end ; 
 return retval ;
end findgroup ;
/

Testing:

select value_, findgroup( value_ ) from samples ;
-- result
VALUE_ FINDGROUP(VALUE_) 
01 a 
05 b 
Z7, Z4, 01 mixed 
09, 08 none 

Solution in SQL

Maybe you don't need a function (or the function is not fast enough for you). Here's another approach, written in SQL. Ideas: {1} break down the values stored in the SAMPLES table via a view, and {2} use this view in a query that JOINs it to the GROUPLISTS, and applies the CASE expressions. ( You can - of course - also code everything into one query, just write the view's code instead of the name SAMPLES_EXPANDED - see the "Alternative" query )

{1} view

create or replace view samples_expanded
as
select unique
 value_
, trim( regexp_substr( value_, '[^,]+', 1, level ) ) item_
from samples
connect by instr( value_, ',', 1, level - 1 ) > 0 ;
-- testing the view
SQL> select * from samples_expanded order by value_;
VALUE_ ITEM_ 
01 01 
05 05 
09, 08 08 
09, 08 09 
Z7, Z4, 01 01 
Z7, Z4, 01 Z4 
Z7, Z4, 01 Z7 
7 rows selected.

{2} query

select 
 value_
, case 
 when sum( in_a ) > 0 and sum( in_b ) > 0 then 'mixed'
 when sum( in_a ) > 0 and sum( in_b ) = 0 then 'group a'
 when sum( in_a ) = 0 and sum( in_b ) > 0 then 'group b'
 else 'none'
 end as groupmembership
from (
 select value_, E.item_, group_
 , case when group_ = 'a' then 1 else 0 end in_a 
 , case when group_ = 'b' then 1 else 0 end in_b
 from samples_expanded E
 left join grouplists G on E.item_ = G.item_ 
) 
group by value_
order by value_
;
-- result
VALUE_ GROUPMEMBERSHIP 
01 group a 
05 group b 
09, 08 none 
Z7, Z4, 01 mixed 

Alternative ( query with inline view )

select 
 value_
, case 
 when sum( in_a ) > 0 and sum( in_b ) > 0 then 'mixed'
 when sum( in_a ) > 0 and sum( in_b ) = 0 then 'group a'
 when sum( in_a ) = 0 and sum( in_b ) > 0 then 'group b'
 else 'none'
 end as groupmembership
from (
 select value_, E.item_, group_
 , case when group_ = 'a' then 1 else 0 end in_a 
 , case when group_ = 'b' then 1 else 0 end in_b
 from (
 select unique
 value_
 , trim( regexp_substr( value_, '[^,]+', 1, level ) ) item_
 from samples
 connect by instr( value_, ',', 1, level - 1 ) > 0 
 ) E
 left join grouplists G on E.item_ = G.item_ 
) 
group by value_
order by value_
;

Dbfiddle here.