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.

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

Attempted statement
-----------------------------------------------------------------

create view bt_area_tree (child_id, parent_id) as
with recursive
parents as (
-- cases where the child has a parent
select rel_entity_id as parent_id, abs_entity_id as child_id
from bt_entity_relations where type_id = 4
union
-- cases where the parent has a child
select abs_entity_id as parent_id, rel_entity_id as child_id
from bt_entity_relations where type_id = 5
),
family as (
-- get everyone, parents and children alike, and their immediate parent if any
select bt_entities.id child_id, parents.parent_id as parent_id
from bt_entities
left join parents on parents.child_id = bt_entities.id
where is_area = 1
union all
-- get everyone's parent, up the tree from children to parents
select family.child_id, parents.parent_id
from family inner join parents on parents.child_id = family.parent_id
)
select child_id, parent_id from family;

Minimal metadata:
-----------------------------------------------------------------
create table bt_entities (
id integer,
is_area check(value in (0,1))
-- there are entities that are not areas, we don't care about them
);
create table bt_entity_relations (
abs_entity_id integer,
rel_entity_id integer,
type_id integer check(value in (4,5))
-- '4' means that rel_entity_id is an immediate parent of abs_entity_id
-- '5' means that rel_entity_id is an immediate child of abs_entity_id
-- which is used depends only on which record the user was looking at when entering the data
-- entities can have multiple parents, but my data contains no cycles (not that this should affect
the CREATE VIEW statement, but it could affect using the query later)
);

CTE's are really cool (thank you!), but hard enough to get "right" that I would hate to have to
repeat them in all of my queries ...

Thanks all!

- Philip