Creating Table from Multiple Colon Separated Values
I have a DB table with a field that stores values that may be colon separated as resulting from a checkbox page item.
1:2
3:4
5:6
My ultimate goal is to turn those values into a table of singular values.
1
2
3
4
5
6
I've gotten close, but can't figure out how to finish the last step. My first step was to create a single query that concatenated all values into one long string. I am able to get
1:2:3:4:5:6
as a result of using the LISTAGG function in the query. My next step was to use a function, STRTAB, that I sourced from this forum to convert the colon delimited string into a result set using the query:
select * from table( strtab( '1:2:3:4:5:6' ))
where the string was derived from the LISTAGG query. The query works when I pass the above string, but if I try to insert the actual LISTAGG query it fails with missing expression. My actual query looks like this:
select * from table( strtab(
select listagg( RESTRICTED, ':' ) WITHIN GROUP ( order by RESTRICTED ) as ID
from SZ_LINKED_PROFILES
where RESTRICTED is not null
and PROFILE in ( select ID
from SZ_PROFILES
where ACCOUNT = :APP_ACCOUNT )))
Anyone know how I can complete the last step? I know I can do this with pl/sql, but I need this to be a single query as I'm using it as the criteria in an IN expression of a larger query.
Thanks in advance for your help!
-Jeff