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)