SQL CTE Not Returning Results

I have the following query to my database. The query returns a result set with a few rows in SSMS, but no rows are returned in Ignition. Thoughts?

q = \
	"""	
	WITH Hierarchy (PersonId, Username, LastName, FirstName, ADPFileNumber, 
					ADPAssociateOID, DepartmentFieldId, Role, Email, SupervisorPersonId,
					IsActive, IsPasswordResetRequired, Created)
	AS
		(
			SELECT	
				PersonId, Username, LastName, FirstName, ADPFileNumber, 
				ADPAssociateOID, DepartmentFieldId, Role, Email, SupervisorPersonId,
				IsActive, IsPasswordResetRequired, Created
			FROM	
				aut_portal.Person
			WHERE	
				SupervisorPersonId = 700
			UNION ALL
			SELECT	
				p.PersonId, p.Username, p.LastName, p.FirstName, p.ADPFileNumber,
				p.ADPAssociateOID, p.DepartmentFieldId, p.Role, p.Email, p.SupervisorPersonId,
				p.IsActive, p.IsPasswordResetRequired, p.Created
			FROM	
				aut_portal.Person p
			INNER JOIN Hierarchy h
			ON	p.SupervisorPersonId = h.PersonId
		)
	
	SELECT	*
	FROM
	(
		SELECT	PersonId, Username, LastName, FirstName, ADPFileNumber, 
				ADPAssociateOID, 
				Department = fdep.FieldAlias,
				Role, Email, SupervisorPersonId,
				IsActive, IsPasswordResetRequired, h.Created
		FROM	Hierarchy h
		LEFT JOIN aut_portal.Field fdep
		ON		h.DepartmentFieldId = fdep.FieldId
	) tbl;
	"""
system.db.runQuery(q, 'NDB')

Do you get any errors when you run this in the DB Browser?

I suspect Ignition is interpreting your query as an update and not a select.

You could try this solution

The solution below suggests running runQuery which you are doing so if the No Count Off solutions doesn’t work for you then you’ll probably have to wrap it in a Stored Procedure as recommended below or use a View and call that.

I am running a similar query using CTE's using a custom Trino driver and Postgres as my default translator. Is there a workaround while still using CTE's without the No Count off feature being avaliable.

Where are you running your query? If a query tag on a recent version of Ignition, note that a default for query type was changed from "Select" to "Auto", which breaks query tags that start with a CTE. Update that new property on the tag if that's the case.

In named queries, you can specify which kind of query it is. In scripted queries, the function itself identifies what kind of query it is.