Oracle Apex multiple value in Column

Question

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.

Storing comma-separated values in a column violates the most basic rules of normalization. That is going to make it much more difficult to work with. The right way to do this would be to create a child table where there were three rows for the three values. Are you sure that you don't want to fix your data model? — Justin Cave
I read the question three times and I still don't understand what you are trying to do. Can you explain the assignment in plain English? (By the way, if the column is Year, Jahr won't work!) — mathguy
@Justin Cave: You're right. A child table could help I will try this. The issue with the data model is that this data is from a public source. Will follow up. — fscherbaum
@mathguy: This is more related to features of APEX. Not so much to SQL. If you are familiar with APEX and still don't understand I will modify my question. — fscherbaum

fscherbaum fscherbaum · Accepted Answer · 2016-10-10T18:07:15

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'

Oracle Apex multiple value in Column

1 Answers