Heiarchial Data in a SINGLE column

Good day all,

I'm working in an environment where our data architecture leaves quite a bit to be desired. Our systems were built upon a somewhat shaky foundation, which sometimes takes us a lot of time to create work arounds.

The current issue is this:

We have employee heiarchial information stored in two columns in our user table (field8 and field 9).

I concatenate that data into one field. The data looks like the following:

83661,7,134123,6,84311,4,80896,3,60849,2,28626,218339,2,13588,2,03093,2,01785,1,,0,,0,,0,,0,01202,1 ^^^
<<userId,supervisor_level,userId,supervisor_level>>

I need to get the first mention of level 2 and/or level3.

So, I wrote a query that uses substr and instr to get level two and one that gets level three, then I use a union to put them in the same result set.

Unfortunately for me, the userId can be anywhere from 5 - 7 characters and is located before the string I'm searching for (",2," or ",3,"). Because of that, I can't simply use INSTR and SUBSTR to get the value that I want.

Also, the string is not consistent in the way that levels are handled (level 3 doesn't always come before level 4 and there can be multiple supervisors on the same level).

Given the data I've shown you, can anyone think of a solution to this problem that doesn't involve a either a cursor or a stand-alone application?

I've been staring at toad for the last four hours and could definately use some help!

Thanks,

Brandon Received on Tue Aug 22 2006 - 12:04:29 CDT