Hi,

> I had a view which unioned three tables together for a particular
> task,
>
> CREATE VIEW FULLMENU(CODE,PARENT,DESCRIPTION,LINK, ORDERING)
> AS
> select menu_id,parent_id,description,
> cast(null as varchar(150)), ordering
> from mainmenu
> union all
> select
> m.stream_id, m.parent, s.name ,
> 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.

Regards,
Arno Brinkman
ABVisie

-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Firebird open source database (based on IB-OE) with many SQL-99 features :
http://www.firebirdsql.org
http://www.firebirdsql.info
http://www.fingerbird.de/
http://www.comunidade-firebird.org/

Support list for Interbase and Firebird users :
firebird-support@yahoogroups.com

Nederlandse firebird nieuwsgroep :
news://80.126.130.81