Netezza does not support CONNECT BY or the recursive version of the WITH statement at this time (not in 6.0 either). The best way to resolve the issue is at data load time. Just create multiple tables instead of shoving everything into a single table... the performance is better using that method anyway. Alternatively you can create a view that uses multiple self joins but the queries are going to run slower that way. This is of course assuming that you have a set number of hierarchy levels which I've found to almost always be the case.
Even if the data is already loaded, it should be very quick and simple to restructure the data as suggested by Shawn above on the Netezza system.. give it a try!
There was a presentation on this at Enzee Universe.
I'd disagree about this being a good solution, however: If you have a small number of hierarchy levels, the multiple table strategy works IF you ensure that the index columns are properly distributed to the same node.
If you have an arbitrarily deep hierarchy, the self joins rapidly become very very complex You can use a series of temp tables instead possibly.
