> >>This query works fine on by DB:
> >>
> >>select pensionPlan_ID from DB union
> >>select pensionPlan_ID from DC
> >>
> >>but this one does not:
> >>
> >>select * from PensionPlans where PensionPland_Id in
> >>(select pensionPlan_ID from DB union
> >>select pensionPlan_ID from DC)
> >>
> >>It does not like the word 'union'
>
> >Nope. Subqueries must return scalar output (one row, one column)
> >and a union is by nature 2 or more rows ---> so verboten.
> >
> >
> One column, yes. But surely not one row. It depends on the context.
> In this context, what's to stop me doing :
>
> Without a UNION, this sort of thing works fine, I do it all the
> time.

Jonathan, have you ever tried writing

select * from PensionPlans where PensionPland_Id in (5 union 7)

I do not think it will work, because I have never heard of that
syntax. There are at least two ways of writing a statement doing what
you want, but it cannot be written using your syntax. The closest to
your syntax would be

select * from PensionPlans where
PensionPland_Id in (select pensionPlan_ID from DB) or
PensionPland_Id in (select pensionPlan_ID from DC)

The way I prefer to write this kind of statements is

select * from PensionPlans PP where
EXISTS(select * from DB where PP.PensionPland_Id=DB.pensionPlan_ID) or
EXISTS(select * from DC where PP.PensionPland_Id=DC.pensionPlan_ID)

The reason I prefer this, is simply that I became accustomed to it
some time ago when using IN (<subselect>) on huge datasets was
extremely time consuming.

HTH,
Set