Subject Re: [firebird-support] CREATE VIEW ... WITH RECURSIVE ... (error)
Author Vlad Khorsun
> I'm using FB2.1rc1. I haven't seen any bug reports indicating this had been reported, or fixed in
> FB2.1rc2.
> The SELECT query itself runs fine (finally), but creating a VIEW out of it does not. Error message
> and query are below.
> Does FB2.1 support creating views based on common table expressions (CTE)'s yet? Am I just getting


> a strange error that indicates it thinks the whole WITH clause is part of the new view name,
> because it's not parsing it correctly? When it complains about long object names, it doesn't spit
> back out what it thinks you meant, so it's hard to tell.

Agree, this is not user friendly.

> Putting doublequotes around the view name, adding/removing the desired view column names, and
> adding AS clauses to the final SELECT didn't help.
> Error message:
> -----------------------------------------------------------------
> Message: isc_dsql_execute2 failed
> SQL Message : -607
> This operation is not defined for system tables.
> Engine Code : 335544351
> Engine Message :
> unsuccessful metadata update
> Name longer than database column size

When query is processed engine assigned name to each context (relation, derived table,
aggregation\union map, etc). When you defined a VIEW this info is stored in RDB$VIEW_RELATIONS.
So its limited by maximum metadata name length. For derived tables (CTE internals is DT) context
names for tables inside it consist from table name\alias concatenated with DT name. So, all you need
to create your VIEW is just use shorter CTE names. Also you may use short alias for tables inside

In your case long context name is :

is a context name of table in main query

is a context name of CTE "parents" used in CTE "family"

is a context name of relation "bt_entity_relations" used in CTE "parents"