Hi,
We're using ODI 12c and I'd like to implement the CONNECT BY statement. The code is as follows:
SELECT DISTINCT ANAME
, trim(regexp_substr(ANAME,'[^,]+', 1, LEVEL) ) NAMEPART
FROM SOMETABLE
CONNECT BY regexp_substr(ANAME, '[^,]+', 1, level) is not null
;
It converts
'ABC, DEF, GHI'
into
'ABC, DEF, GHI', 'ABC'
'ABC, DEF, GHI', 'DEF'
'ABC, DEF, GHI', 'GHI'
I've tried to put the CONNECT BY in a FILTER as was suggested in an old posting for OWB. Since ODI (or the KM?) wraps the statement in brackets and places it in an AND construction, this doesn't work.
Does anyone have a solution that does not involve changing the Knowledge Module or using a database View?
Regards,
Martin