Just a question that has risen in this question:
The sample query from this answer does work with 184.108.40.20626 (on Windows) but raises error -921 ("Invalid recursive query") with 220.127.116.1169.
That error is documented with the following "Probable cause":
The query does use UNION ALL etc.
SELECT ...CAST (ROW_NUMBER() OVER (PARTITION BY e.manager_ID ORDER BY e.employee_id)
If my understanding of the restrictions for the references to the common table expression in the iterative query is correct, then I would think the query should be allowed.
So the question is: Is the new behaviour (i.e. raising -921) a bug fix or a bug?
The "QTS-711791 Recursive queries containing window aggregate functions were not prohibited" fixed a bug in the SQL Anywhere server which explained your issue:
SQL Anywhere EBF
SA 12.0.1 3758
SA 11.0.1 2836
Recursive queries can not contain aggregate functions. When written using a window (OVER clause), aggregates were improperly allowed. In some cases this could lead to a crash under certain system conditions. This has been fixed: queries with windowed aggregate functions now correctly give an error.
answered 09 Oct '12, 14:06
Are you using an aggregate function in your query?
If so, they perhaps you are getting the error due to QTS 711791 that was made in 18.104.22.16858 and 22.214.171.12436:
This is another way to achieve your sorting without using window aggregate functions. I assumed that sort-string is only used for sorting and it is not needed in the result set.
WITH RECURSIVE employee_hierarchie ( level, sort_string, manager_id, employee_id, name ) AS ( SELECT CAST ( 1 AS INTEGER ) AS level, CAST ( e.employee_id AS LONG VARCHAR) AS sort_string, e.manager_id AS manager_id, e.employee_id AS employee_id, e.name AS name FROM employee e where e.employee_id = e.manager_id UNION ALL SELECT h.level + 1, h.sort_string || '-' || CAST ( e.employee_id AS LONG VARCHAR), e.manager_id, e.employee_id, e.name FROM employee_hierarchie h INNER JOIN employee e ON h.employee_id = e.manager_id WHERE e.employee_id <> e.manager_id) SELECT level, manager_id, employee_id, name FROM employee_hierarchie ORDER BY sort_string;
answered 09 Oct '12, 14:42