Parse GL account string:
select
glaccount,
regexp_substr(glaccount, '[^-]+', 1, 1) glseg1,
regexp_substr(glaccount, '[^-]+', 1, 2) glseg2,
regexp_substr(glaccount, '[^-]+', 1, 3) glseg3,
regexp_substr(glaccount, '[^-]+', 1, 4) glseg4,
regexp_substr(glaccount, '[^-]+', 1, 5) glseg5,
regexp_substr(glaccount, '[^-]+', 1, 6) glseg6
from
workorder
order by
length(glaccount) desc nulls last
classl1 = trim(regexp_substr(hierarchypath, '[^\]+', 1, 1)),
classl2 = trim(regexp_substr(hierarchypath, '[^\]+', 1, 2)),
classl3 = trim(regexp_substr(hierarchypath, '[^\]+', 1, 3)),
classl4 = trim(regexp_substr(hierarchypath, '[^\]+', 1, 4));
--the TRIM() function removes the spaces around the backslash delimiter
I'd be happy to hear about any issues/mistakes.
Cheers.