E9.2 F0150 Address Organization Structure Master - Find Top Parent with SQL

[Shannon.Inge]

In order to do some reporting outside of E1, I need to find the top parent for any given child in F0150. The hierarchies vary in the number of levels and the child entered may not be the bottom child. How can I accomplish this in a SQL query?

I'm hoping someone has done this before and I won't have to reinvent the wheel, but if I figure it out before I get an answer I'll be sure to post it for future reference.

 

[Shannon.Inge]

After some digging I found that our hierarchies are no more than 3 levels (2 records) deep. The following is not pretty, but it works. It could be expanded if more levels are needed.

select case
when (select count(*) from proddta.f0150 L1P where maan8 = (select mapa8 from proddta.f0150 L1C where maan8 =<CHILD>)) = 1
then (select mapa8 from proddta.f0150 L1P where maan8 = (select mapa8 from proddta.f0150 L1C where maan8 = <CHILD>))
when (select count(*) from proddta.f0150 L2C where maan8 = <CHILD>) = 1
then (select mapa8 from proddta.f0150 L2C where maan8 = <CHILD>)
else <CHILD>
end

If someone has a better way to accomplish this, I would love to see it!

 

[DaveWagoner]

Which SQL server are you using? There might be fun tricks depending