SQL CTE Not Returning Results

[cmaynes]

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')

[code_skin]

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

Microsoft SQL Server - "Result set was generated for update"

If using Ignition (or FactorySQL/PMI, for that matter) with SQL Server and you run into the following error in various places: you need to turn off the “no count” option for the server. It appears that the different versions of SQL Server are inconsistent as to whether it is on by default. To change it: Go into the SQL Server management console Right-click on the server and select “properties”. Select “connections” Under “Default Connection Options”, turn off “no count”. The change will on…

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.

A result set was generated for update - SQL Query

Where are you trying to run it from? Wherever it is, it is trying to run it as an “update”, when you really want it to run like a select. You’ll likely need to use the system.db.runQuery scripting function or something similar. And in that case, it’ll probably be easier to wrap it up in a stored procedure and just call that. Regards,

[Hayes_Bentley]

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.

[pturmel]

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.