Question Re: Turning a recursive query into a view raises SQLCODE -812 in Technology Q&A

Turning a recursive query into a view raises SQLCODE -812

VolkerBarth — Wed, 03 Oct 2012 16:20:38 GMT

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.

Re: Turning a recursive query into a view raises SQLCODE -812

VolkerBarth — Wed, 03 Oct 2012 16:33:07 GMT

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

Re: Turning a recursive query into a view raises SQLCODE -812

Former Member — Wed, 03 Oct 2012 16:38:59 GMT

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 !

Re: Turning a recursive query into a view raises SQLCODE -812

VolkerBarth — Wed, 03 Oct 2012 16:54:03 GMT

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

Re: Turning a recursive query into a view raises SQLCODE -812

Breck_Carter — Wed, 03 Oct 2012 17:13:51 GMT

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 🙂

Re: Turning a recursive query into a view raises SQLCODE -812

Breck_Carter — Wed, 03 Oct 2012 17:14:20 GMT

Show us the whole CREATE VIEW please.

Re: Turning a recursive query into a view raises SQLCODE -812

VolkerBarth — Wed, 03 Oct 2012 17:19:52 GMT

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

Re: Turning a recursive query into a view raises SQLCODE -812

Former Member — Wed, 03 Oct 2012 17:51:06 GMT

I am using 12.0.1.3769 on Windows as well.

Re: Turning a recursive query into a view raises SQLCODE -812

VolkerBarth — Wed, 03 Oct 2012 17:53:48 GMT

Here you are:)

Re: Turning a recursive query into a view raises SQLCODE -812

VolkerBarth — Wed, 03 Oct 2012 18:23:38 GMT

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

Re: Turning a recursive query into a view raises SQLCODE -812

Breck_Carter — Thu, 04 Oct 2012 17:05:22 GMT

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