Deriving different Levels

Havoc · Post by **Havoc** » Wed Feb 14, 2007 11:58 am

Hi,

I thought i'd post this. I think I found a solution/workaround to the problem mentioned.
Thought we can use a user-defined insert SQL in the Oracle Stage by leveraging the LEVEL pseudocolumn in the Oracle d/b. Its pretty handy..

syntax can be found here:

http://www.psoug.org/reference/connectby.html

Havoc · Post by **Havoc** » Thu Feb 15, 2007 1:50 am

Can someone provide some inputs on this ? Disadvantages or otherwise with regard to this approach?

kumar_s · Post by **kumar_s** » Thu Feb 15, 2007 4:23 am

Hi Havoc,

The option that you found out is more simple. You can just group by based on Parent Dept Child Dept, and get the max(LEVEL) with order by max(LEVEL) during extraction. You can just concatenate subsequent row until you find a change in value using stage variable. Though the LEVEL is in reverse order when compared to you expectation, it is quite simple for your requirement.