Re: Heiarchial Data in a SINGLE column

On 22 Aug 2006 10:04:29 -0700, "Brandon" <brandon.osborne_at_gmail.com> wrote:

>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?

 Does the "no cursor" restriction rule a PL/SQL function (possibly a pipelined one since you want multiple values) out of the picture?

 Given the inconsistencies like "2,28626,218339" then it seems that a procedural approach would be easiest, rather than acres of substr/instr in unions.

-- 
Andy Hassall :: andy@andyh.co.uk :: http://www.andyh.co.uk
http://www.andyhsoftware.co.uk/space :: disk and FTP usage analysis tool