3

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?

3
  • 1
    Which DBMS are you using? You have tagged both Orace and Db2. Also what have you tried so far? DDL always helps too May 1, 2019 at 22:19
  • 1
    My mistake - I didn't mean to tag db2 - I'm using Oracle, standard PL/SQL. May 2, 2019 at 22:00
  • 1
    When you say a list, I presume you mean a comma-delimited string? May 4, 2019 at 7:00

1 Answer 1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.