Re: Heiarchial Data in a SINGLE column

There's nothing wrong with what I'm doing with the data and the architecture is flawed.

As I indicated earlier, the data is stored in two fields which I'm concatenating.

Meaning it looks like the following:

Participant_Id|Platform_Num|First_Name|Last_Name|Level8|Level9

1234|3877|John|Doe|94853,1,34843,2,128345,2,12834,4|47542,5,24852,7
1235|3877|Bob|Marley|94853,1,34843,2,128345,2,12834,4|47542,5,24852,7
1236|3877|John|Smith|94853,1,34843,2,128345,2,12834,4|47542,5,24852,7

So, as you can see I have to concatenate level 8 and level 9 to get the full heiarchy and connect by doesn't work with data in the above format. I'm just stuck with the data that this client is sending us. I've replaced most of our client's data upload processes with bizTalk so the data doesn't come in this way anymore. This particular beast is just resistant to change.

PS: DA Morgan, before you condescend to someone, why don't you try actually reading the question completely or at least trying to understand the requirements. If you do the same with business requirements, I'd hate to be one of your clients.

Regards,

Brandon
DA Morgan wrote:
> Brandon wrote:
> > 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>>
>
> What is the point of concatenation?
>
> I don't see anything wrong with the architecture. I see something wrong
> with what you are doing with it.
>
> Leave the data alone and look at the syntax of CONNECT BY.
> Syntax at http://tahiti.oracle.com
> Demos in Morgan's Library at www.psoug.org
> --
> Daniel A. Morgan
> University of Washington
> damorgan_at_x.washington.edu
> (replace x with u to respond)
> Puget Sound Oracle Users Group
> www.psoug.org
Received on Tue Aug 22 2006 - 14:53:50 CDT