Turning a recursive query into a view raises SQLCODE -812

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.

asked 03 Oct '12, 16:20

Volker Barth
39.3k●355●539●809
accept rate: 33%

edited 03 Oct '12, 18:33

Comment Text Removed

Show us the whole CREATE VIEW please.

(03 Oct '12, 17:14) Breck Carter

Replies hidden

Here you are:)

(03 Oct '12, 17:53) Volker Barth

flat view

2 Answers:

active answers oldest answers newest answers popular answers

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

permanent link

answered 03 Oct '12, 16:33

Volker Barth
39.3k●355●539●809
accept rate: 33%

edited 03 Oct '12, 16:34

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

flat view

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

permanent link

answered 03 Oct '12, 17:13

Breck Carter
32.5k●539●724●1050
accept rate: 20%

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

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

flat view

Your answer

toggle preview

community wiki:

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

*italic* or _italic_
**bold** or __bold__
link:[text](http://url.com/ "title")
image?![alt text](/path/img.jpg "title")
numbered list: 1. Foo 2. Bar
to add a line break simply add two spaces to where you would like the new line to be.
basic HTML tags are also supported

learn more about Markdown

Question tags:

sa-12 ×438
error-message ×43
view ×31
recursion ×18

question asked: 03 Oct '12, 16:20

question was seen: 3,366 times

last updated: 04 Oct '12, 17:05

SAP SQL Anywhere

Turning a recursive query into a view raises SQLCODE -812

Follow this question

Related questions