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

image.png

Stumped on a query, hoping somone can give me some guidance. I think it’s possible with connect by?

• I have a hierarchial table of arbitrary depth. 
• For example below, lets say employee &gt; supervisor hierarchy.
• I have an additional column for “color”. 
• I want a report with all employees, where each employee has either it’s OWN color, or the color inherited from it’s parent (recursively). 
• So, in example below, employee 2 inherits “blue” from their supervisor 1. Then employee 3 inherits blue from their supervisor 2 (basically rolling up to 1). 
• Note employee 6 DOES NOT inherit from 5, because they already have an assigned color directly to them.
Anyone know how to accomplish a query where it recursively looks up the hierarchy for a value?

Well I don’t know how to recursively search up through parents, but if you know some sort of sane maximum depth you could do this with a bunch of JOINS and a COALESCE.

say for example, you knew you’d not have anything deeper than 10 levels

then something like this (syntax NOT checked…)

```SELECT empid, 
COALESCE(
	emp.custentity_colour, 
	p.custentity_colour, 
	gp.custentity_colour, 
	ggp.custentity_colour, 
	gggp.custentity_colour, 
	ggggp.custentity_colour, 
	gggggp.custentity_colour, 
	ggggggp.custentity_colour, 
	gggggggp.custentity_colour, 
	ggggggggp.custentity_colour
) AS colour

FROM employee emp
LEFT JOIN employee p ON (p.id = emp.supervisor)
LEFT JOIN employee gp ON (gp.id = p.supervisor)
LEFT JOIN employee ggp ON (ggp.id = gp.supervisor)
LEFT JOIN employee gggp ON (gggp.id = ggp.supervisor)
LEFT JOIN employee ggggp ON (ggggp.id = gggp.supervisor)
LEFT JOIN employee gggggp ON (gggggp.id = ggggp.supervisor)
LEFT JOIN employee ggggggp ON (ggggggp.id = gggggp.supervisor)
LEFT JOIN employee gggggggp ON (gggggggp.id = ggggggp.supervisor)
LEFT JOIN employee ggggggggp ON (ggggggggp.id = gggggggp.supervisor)```

else you are probably going to have to this w/script.

Give this a try: (<https://asktom.oracle.com/pls/apex/asktom.search?tag=inherit-value-from-parent>)
Compared to a lot of other ways of doing it, this one actually worked in SuiteQL for me.
```SELECT
	e.id,
    e.entityId,
    e.supervisor,
    e.color
    ,nvl(
      	color,
        (SELECT color
         FROM employee e2
         WHERE color IS NOT null
         START WITH e2.id = e.supervisor
         CONNECT BY PRIOR e2.supervisor = e2.id AND PRIOR color IS NULL)
    ) as inherited_color
FROM employee e
ORDER BY id```

SuiteQL is wierd, I cannot run
```SELECT id
FROM employee
WHERE custom_field is not null
CONNECT BY PRIOR supervisor = id```
but I can run
```
SELECT id
FROM employee
WHERE supervisor is not null
CONNECT BY PRIOR supervisor = id```
And by that the inner SELECT of previous query shouldn't work?? But as a subquery, it does.

<@UP0GS40AF> Thank you! I didn’t think of trying the subquery in the select portion but that seems to work for me.