Subject Re: [firebird-support] Views with Unions
Author Arno Brinkman

> I had a view which unioned three tables together for a particular
> task,
> AS
> select menu_id,parent_id,description,
> cast(null as varchar(150)), ordering
> from mainmenu
> union all
> select
> m.stream_id, m.parent, ,
> cast('/knowledge/'
> || s.stream_id as varchar(150)), m.ordering
> from streammenu m join knowledgestreams s
> on s.stream_id = m.stream_id
> union all
> select tool_id, parent_id,description,
> cast(link as varchar(150)), ordering
> from systemtools;
> If I used this view anywhere else like
> select * from fullmenu where code = '27' the view is executed
> without using any indexes, ie plan natural. Even though each source
> code field has an index.

The problem is that internally the where conditions aren't delivered into
the select-statements inside the union.
This is a known issue to me and on my taks-list for the next firebird
version. I understand that this doesn't solve it for you at moment, so
you've to workaround this with a direct query or a stored procedure.

Arno Brinkman

Firebird open source database (based on IB-OE) with many SQL-99 features :

Support list for Interbase and Firebird users :

Nederlandse firebird nieuwsgroep :