Search code examples
oracleoracle-apexoracle-apex-5

Oracle Apex multiple value in Column

I'm having a text field with comma sepeated values in my Oracle Apex page (e.g. "F01C, F01D, G01A"). The values are stored in only one column of my table (Table: HC_DEFHANDBUCH_B5 Column DRG).

------
PK_ID   OPS     MDC         DRG                 OPS_FILL    MDC_FILL    YEAR
214098  1-100    2          C01A, C01B, C14Z    1-100       2           2017
214099  -       15          P67D                1-100       15          2017
214100  1-204.2 15          P67D                1-204.2     15          2017
214101  1-204.3 15          P67D                1-204.3     15          2017
-------

I tried to run a query in an interactive report to select all DRGs for PK_ID '214098' from the Table HC_GDRG_FPK which is having one Row per DRG:

Select * FROM HC_GDRG_FPK a
WHERE a.DRG IN (Select '(''' || REPLACE(b.DRG,', ',''', ''') || ''')' FROM HC_DEFHANDBUCH_B5 b WHERE b.PK_ID='214098')
AND a.Jahr='2017'

Does anyone have an idea how to get this working? I get it working on a single value.

Thanks in advance.

Solution

  • I could finally solve it with regular expressions. @Justin Cave: Thanks for the hint.

    select * from HC_GDRG_FPK where DRG in (
select regexp_substr(:P39_CALL_DRGS,'[^,]+', 1, level) from HC_DEFHANDBUCH_B5
connect by regexp_substr(:P39_CALL_DRGS, '[^,]+', 1, level) is not null )
AND Jahr ='2017'