AnsweredAssumed Answered

DWH Multi Value Lookup Filtering

Question asked by AustinCA on Mar 29, 2016
Latest reply on Mar 2, 2017 by Rama06

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?

 

 

Thanks,

 

 

Austin

 

 

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*/

FROM   dual

where 1=1

connect by regexp_substr(' TEST1 ; TEST2 ','[^;]+',1,level) is not null /* String to break apart*/

 

Results

A  B TEXT_2

-----------

A  B  TEST1

A  B  TEST2

Outcomes