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

[VolkerBarth · Accepted solution]

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:

SELECT * can only be used in the main query of the CREATE VIEW statement. Derived tables and subqueries must use full expressions in the SELECT list, rather than the * operator. For example, CREATE VIEW V AS SELECT * FROM T, (SELECT * FROM R) AS DT is incorrect, as the derived table DT is specified using SELECT * rather than using a SELECT list with specified expressions. Similarly, an implicit SELECT * used in a derived table is not allowed. For example, CREATE VIEW V AS SELECT * FROM T, LATERAL(proc(T.A.)) AS DT has an implicit SELECT *, as LATERAL(proc(T.A.)) is a short form for LATERAL(SELECT * FROM proc(T.A.)) and is therefore not allowed in the view definition.

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:

Probable cause
The specified alias from the derived table's AS clause has no matching expression from the SELECT statement for that derived table. Ensure that each SELECT list item has a matching alias in the derived table's AS clause, and vice-versa.

That made me leave another personal learning experience here:)

[Former Member · Accepted solution]

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 !

[VolkerBarth · Accepted solution]

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

[Former Member · Accepted solution]

I am using 12.0.1.3769 on Windows as well.

[VolkerBarth · Accepted solution]

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