This is a variation of the sample query used in this recent FAQ on recursive queries: In contrast to the sample query from the question (not from the answer), it includes all columns from the underlying table, so it uses "SELECT *" for simplicity in the recursive queries: WITH RECURSIVE employee_hierarchie ( level, employee_id, manager_id, name, salary ) AS ( SELECT CAST ( 1 AS INTEGER ) AS level, e.* FROM employee e where e.employee_id = e.manager_id UNION ALL SELECT h.level + 1, e.* FROM employee_hierarchie h INNER JOIN employee e ON h.employee_id = e.manager_id WHERE e.employee_id <> e.manager_id) SELECT * FROM employee_hierarchie ORDER BY level, employee_id Whereas this works fine as a query, turning that into a view by simply adding a CREATE VIEW viewname AS statement like CREATE VIEW V_employee_hierarchie AS WITH RECURSIVE employee_hierarchie ( level, employee_id, manager_id, name, salary ) AS ( SELECT CAST ( 1 AS INTEGER ) AS level, e.* FROM employee e where e.employee_id = e.manager_id UNION ALL SELECT h.level + 1, e.* FROM employee_hierarchie h INNER JOIN employee e ON h.employee_id = e.manager_id WHERE e.employee_id <> e.manager_id) SELECT * FROM employee_hierarchie -- ORDER BY should be avoided, as there's not SELECT TOP/FIRST ORDER BY level, employee_id does not work, but raises the surprising error SQLCODE -812 with message:
This message really doesn't seem to make sense to me, particulary as the select list obviously does fit, otherwise the recursive query wouldn't work. So what's the matter here? - I've tested with the newest EBF 12.0.1.3769, too. |
After much confusion, I've found out that the problem is due to the fact that views don't allow "SELECT *" in the underlying query unless in the top level query block, and in the sample the "SELECT *" apparently appears inside subqueries... - so that is a violation of the documented CREATE VIEW restrictions:
So, replacing the "SELECT e.*" with an explicit column list in the recursive query's two UNIONed query blocks does solve the problem and makes the view createable. However, IMHO the error message does seem misleading here, and the documented probable cause, too:
That made me leave another personal learning experience here:) I just added a comment that it worked fine for me with just a SELECT * FROM V_employee_hierarchie while you were adding your response !
(03 Oct '12, 16:38)
Derli Marcochi
Replies hidden
So you're saying the view as stated in the query does work for you? What version are you using? (I've tested with 12.0.1.3726 on Windows.)
(03 Oct '12, 16:54)
Volker Barth
I am using 12.0.1.3769 on Windows as well.
(03 Oct '12, 17:51)
Derli Marcochi
Replies hidden
I've just tested with 12.0.1.3769, and I still get the error message (and if I'm reading the docs correctly, the view should fail with an error - however probably a better message...).
(03 Oct '12, 18:23)
Volker Barth
|
The final SELECT * FROM employee_hierarchie ORDER BY level, employee_id should not be part of the CREATE VIEW... it is the actual query that USES the view :) 1
I have to contradict: The "ORDER BY clause" should be avoided (as there is no SELECT TOP/FIRST), however, without a top level select a view cannot be created, and without that SELECT, there's only a recursive common table expression, nothing else. Trying to omit the SELECT ... leads to a -131 error ("Syntax error near (end of line)").
(03 Oct '12, 17:19)
Volker Barth
Replies hidden
1
My mistake... it is also contradicted by this statement "The previous query can be turned into a permanent view by replacing the outer SELECT with a simple "SELECT *" and giving it a name in a CREATE VIEW statement..." in http://sqlanywhere.blogspot.com/2012/04/example-recursive-union.html
(04 Oct '12, 17:05)
Breck Carter
|
Show us the whole CREATE VIEW please.
Here you are:)