Thank you Daniel,

Your suggested technique does work. Use of distinct keyword and
replacing every "union" with "or" construct does bring the right
result in our model. I attach the sql below.

Kind regards,
berhane.

select
distinct
x.*
from
element x
where
x.element_id in
(select b.element_id
from entity a
inner join element b on (b.object_id = a.entity_id)
and (b.element_type_id =2)
where(a.system_id = 1))
or
x.element_id in
(select b.element_id
from attribute a
inner join element b on (b.object_id = a.attribute_id)
and (b.element_type_id=3)
inner join entity c on (c.entity_id = a.entity_id)
where(c.system_id = 1));

--- In ib-support@yahoogroups.com, Daniel Rail <daniel@a...> wrote:
> Hi,
>
> At April 23, 2003, 14:45, bkehase2000 wrote:
>
> > I am trying the make the following work!
>
> > During a summarisation process, I am extracting sets of IDs from
a
> > table, which I wish to use as an input to a further query. At the
> > moment, I am using the following technique - queries UNIONED
> > together, which I wish to use as a subquery. In my actual code, I
> > have a dynamic number of additional subsets I "union on".
>
> > The UNION statement below works fine by itself, producing a valid
> > list. However, the whole does not - FB gives the error at the
bottom.
>
> > I am thinking along the following lines :-) :
> > 1) My syntax may be wrong
> > 2) Unions cannot be used within subqueries (?)
>
> Yes, unions are not accepted in subqueries.
>
> > 3) Perhaps there is a better way of doing this.
>
> For the result that you are seeking, try the following statement:
>
> select
> x.*
> from
> element x
> where
> ((x.element_type_id=2)
> and exists (select 1
> from entity
> where (entity.system_id=1)
> and (entity.entity_id=x.element_type_id)))
> or ((x.element_type_id=3)
> and exists (select 1
> from attribute
> join entity
> on (entity.entity_id=attribute.entity_id)
> where (entity.system_id=1)
> and
> (attribute.attribute_id=x.object_id)));
>
> The statement might have typos, I simply written up the statement
> without testing it.
>
> > 4) What is the maximum length of a SQL statement - at the moment
I am
> > solving this by using two passes, where I generate a piece of SQL
> > with an IN construct - but this list of values could grow very
large -
> > hence this overall problem.
>
> I rarely use the IN predicate, unless the values used are from
another
> table and a query can't give me the correct result set.
>
> --
> Best regards,
> Daniel Rail
> Senior System Engineer
> ACCRA Group Inc. (www.accra.ca)
> ACCRA Med Software Inc. (www.filopto.com)