We have a few multi value look-ups for our projects and need to filter on them. It appears the DWH stores them as 'ITEM1; ITEM2: etc' in varchar?
When there is more than one value the filter essentially fails.
I tried regex and connect by to pull them apart and re-join but it causes an error (Even with a simple test query).
(14.3, On demand, Jasper6.#)
Any thoughts or ideas on where to go or what to try? Is something overlooked?
Also the DWH for on demand is Oracle? Why would regex/connect by cause errors?
No issues when running the same test query against the ppm/prod DB - Errors in Jasper.....
SELECT 'A', 'B',
TRIM(regexp_substr(' TEST1 ; TEST2 ','[^;]+',1,level)) as TEXT_2 /* String to break apart*/
connect by regexp_substr(' TEST1 ; TEST2 ','[^;]+',1,level) is not null /* String to break apart*/
A B TEXT_2
A B TEST1
A B TEST2