Turning a recursive query into a view raises SQLCODE -812

[VolkerBarth]

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:

The SELECT list for the derived table 'employee_hierarchie' has no expression to match 'manager_id'

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.

[Breck_Carter]

Show us the whole CREATE VIEW please.

[VolkerBarth]

Here you are:)

[Breck_Carter]

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 🙂

[VolkerBarth]

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)").

[Breck_Carter]

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