The code is performing hierarchical recursion on the clients table. The condition isParent=’False’ seems to suggest you’re attempting to remove non-root rows from the base of the recursion. So I added the condition to the top SELECT statement (above the UNION ALL) only. Also, I removed the unnecessary brackets and reformatted the code for readability (at least imo).
with t(clientid, code, cname, rootid, active) as ( select clientid, code, cname, clientid as rootid, active from clients mt where sub = 0 and isParent='False' union all select mt.clientid, mt.code, mt.cname, t.rootid, mt.active from clients mt join t on t.clientid = mt.sub), roots(rootid) as ( select rootid from t where cname like '%abb%' or code like '%abb%') select t.code, t.cname, t.clientid from t join roots rt on rt.rootid = t.rootid where t.active='true';
CLICK HERE to find out more related problems solutions.