This site was built to compliment the existing NetSuite Professionals Slack Community by adding a means for members to easily find answers to common questions and to create a searchable database of those questions and answers.

NetSuite Professionals

Hi, I'd like to get the full hierarchy for a classification.
The function "CONNECT BY PRIOR id = parent" does work in SuiteQL and I can get the LEVEL from it and also get all children using START WITH parent IN(X,Y)
What I can't get to work is SYS_CONNECT_BY_PATH() to write out the full hierarchy "Parent : Child : GrandChild". Is there a trick to it?

There is also a `builtin.hierarchy` function you can try your luck with. I gave it a shot a few months ago but didn't succeed in getting anything working. <https://docs.oracle.com/en/cloud/saas/netsuite/ns-online-help/article_161950565221.html|https://docs.oracle.com/en/cloud/saas/netsuite/ns-online-help/article_161950565221.html>

I ended up just giving up and doing a bunch of self joins

Thanks, what I want to do is to filter out all classes where any of the parents/grandparents are inactive. So I thought I then could use:
`where SYS_CONNECT_BY_PATH(isinactive,' : ') not like('%T%')`
builtin.hierarchy just seems to get the hierarchy by the name unfortunately

```SELECT name, id, parent, isinactive, BUILTIN.HIERARCHY(parent, 'SELF_DISPLAY') as path
FROM classification```
This worked, but isn't what I need unfortunately.

Hideous, but it works.
```SELECT 
C.id,
C.name,
C.parent,
C.isinactive || P1.isinactive || P2.isinactive || P3.isinactive || P4.isinactive || P5.isinactive || P6.isinactive || P7.isinactive as all,
FROM classification C
left join classification P1 ON C.parent = P1.id
left join classification P2 ON P1.parent = P2.id
left join classification P3 ON P2.parent = P3.id
left join classification P4 ON P3.parent = P4.id
left join classification P5 ON P4.parent = P5.id
left join classification P6 ON P5.parent = P6.id
left join classification P7 ON P6.parent = P7.id
WHERE (C.isinactive || P1.isinactive || P2.isinactive || P3.isinactive || P4.isinactive || P5.isinactive || P6.isinactive || P7.isinactive) not like('%T%')```

I wonder if its faster to pull out all projects without joins and then recursively remove all that are inactive and it's children with javascript.

&lt;10 milliseconds to do it for 3000 records in the browser console at least.

The difference from running the query with the seven joins or without is also negligible