Sql Server - dynamic recursive query error "Return: 8095 SELECT item number is outside valid range f
We recently migrated to SQL Server and I need to rewrite a dynamic view used to retrieve all employee that report to the current operator. As you will see below, the new query correctly retrieves all staff reporting to the user.
However, the problem we are experiening is that when I define %OperatorID as the search critera, the system issues the error. "Return: 8095 - A SELECT item number is outside valid range for query (SQLSTATE PS095)"
This query works outside of PeopleSoft, using a query tool.
I'm not certain if the following is correctly defined in the dynamic view.
with departmentcte(OPRID, EMPLID, POSITION_NBR, REPORTS_TO, LASTNAME, FIRSTNAME, LEVEL, TREEPATH)