Better way of using string CSV of codes to drive SQL record matching Where clause?

[Vectorspace]

I.e. if I have this:
codesCsv = 'a,b,c,d,e';

and I want to do this in SQLRPGLE:

exec sql
  select * from myTable t
   where t.code "is one of the codes in :codesCsv"

I have been using this method:

exec sql
  with codesList as (
    select element from table(
      systools.split(:codesCsv,',')
    )
     where :codesCsv <> '' -- blank filter means match all, so don't bother invoking split()
  )
  select * from myTable
   where (
     :codesCsv = '' -- blank filter means match all
     or codes in (select * from codesList);
   )

Which works just fine, but it gets a bit cumbersome if I have several columns with CSV list filters

I wonder if there's a better way?

[MFisher]

Can you load the codes into a Temp table in individual rows, instead of a character string ? Then use WHERE EXISTS or equivalent against your temp table.

[Vectorspace]

The codes being received into the procedure as CSV we can't change.

I could read them into a temp table and then use either a where exists or an inner join, but I'd still need to use systools.split() so I think it would just make it even more complex:

exec sql
create or replace table qtemp/myTempTable (
code char(1)
);

exec sql insert into qtemp/myTempTable
select element from table(
systools.split(:codesCsv,',')
)
where :codesCsv <> ''; -- blank filter means match all, so don't bother invoking split()​

exec sql select * from myTable t
where (
:codesCsv = '' -- blank filter means match all
or exists (select 1 from qtemp/myTempTable x where t.code = x.code)
);

Maybe way I'm doing it is the best way, I was just curious if there was a better way

[UserName10]

You could load a temp table via RPG using the %split opcode.