Why does CONNECT BY fail in SuiteQL with no_root_node?

Because the statement did not get as far as your hierarchy. START WITH ... CONNECT BY PRIOR failed to parse in both probes here, so nothing in it executed. Pull the parent links out flat and assemble the tree in your own code.

Minimal reproduction

SELECT id, name, LEVEL FROM account START WITH parent IS NULL CONNECT BY PRIOR id = parent FETCH FIRST 20 ROWS ONLY

Verbatim error text:

N/query.runSuiteQL error: Search error occurred: Failed to parse SQL [SELECT id, name, LEVEL FROM account START WITH parent IS NULL CONNECT BY PRIOR id = parent FETCH FIRST 20 ROWS ONLY]: syntax error, state:985(10102) near: parent(1,48, token code:0)
no_root_node(-1000) near: no root node(0,0, token code:0)
syntax error, state:0(10102) near: FETCH(1,92, token code:0)
no_root_node(-1000) near: no root node(0,0, token code:0)

One detail in that statement is worth flagging before you copy it: account has no name column. The run enumerated all 42 of its columns, 35 standard ones plus 7 custom custrecord_* fields, and the name-ish standard columns are fullname, accountsearchdisplayname, displaynamewithhierarchy and acctnumber. There is no plain name. That is not what this error is about. An unresolvable column in a statement that also carries FETCH FIRST is most often reported as a syntax error at the FETCH token instead, which is a different message from the one above.

A second shape was run as well: the select list is replaced with id, SYS_CONNECT_BY_PATH(name, ' : ') and the limit is FETCH FIRST 10 ROWS ONLY. The error is structurally identical and only the character offsets move.

The fix

Do not ask the database to walk the tree. Ask it for the edges:

SELECT id, parent, fullname FROM account

Every row carries its own parent id, so one pull is the whole edge list. Roots are the rows where parent is null, and every parent id in the result is also an id in the same result, so parent names resolve in memory with no join at all.

Scope note on the fix: the run did not execute this exact statement. What it establishes is that id, parent and fullname are all columns of account.

If you would rather have the database resolve parent names with a self-join, check first that the rows with a null parent survive it. Some outer joins are downgraded here without an error: between transaction and transactionLine, RIGHT JOIN came back inner-like and FULL OUTER JOIN behaved like a LEFT JOIN from the header side. A downgrade of that kind drops exactly the rows a hierarchy walk starts from, and account joined to itself was not among the pairs tested. See the RIGHT JOIN page.

Why this happens

The parse stops at parent, the token right after START WITH, at offset 48 of the statement. That is before CONNECT BY and long before FETCH. FETCH FIRST n ROWS ONLY is accepted on its own by this engine, so the second syntax error in that block, the one pointing at FETCH, is fallout from a parse that had already failed and not a second thing to fix.

no_root_node reads like a complaint about your hierarchy having no root row. It is not about your data. The identical no_root_node(-1000) near: no root node(0,0, token code:0) fragment comes back in the REST error envelope for SELECT id FROM transaction LIMIT 5, a statement with no hierarchy anywhere in it.

How to tell if you are affected

Run the construct against a table you know, with real column names and no row limit:

SELECT id, fullname FROM account START WITH parent IS NULL CONNECT BY PRIOR id = parent

Two outcomes, and they look nothing alike. A Failed to parse SQL error naming parent means your surface behaves like this run's and hierarchical syntax is not available to you. Rows back, with the account tree in them, means your surface accepts something this one refused, and that is worth writing down.

Leave the FETCH FIRST clause off, as above. That is deliberate: with FETCH FIRST present, a column that does not resolve is most often reported as a syntax error at the FETCH token, and you would not be able to tell that failure apart from this one. It is not the only outcome an unresolvable column can produce, so the rule to follow is the action, not the diagnosis: if SuiteQL blames your FETCH clause, delete the FETCH clause and run again, because the second error names the real problem. id, fullname and parent all exist on account, so nothing in the test above can fail that way.

Scope: both probes ran against the account table through N/query.runSuiteQL on 2026.1, and hierarchical syntax was not tried on the REST endpoint at all. The REST error quoted above is cited only for the no_root_node fragment, and it comes from a different statement. Nothing here tests whether another table or another spelling of a hierarchical query behaves differently, and neither probe reached execution, so LEVEL, PRIOR and SYS_CONNECT_BY_PATH were not checked for support one way or the other.