Re: UNION of disjoint SELECTs based on other SELECT - how??

From: Geoff White <whiteg_at_ccis.adisys.com.au>
Date: Thu, 15 Jul 1999 11:44:29 +0800
Message-ID: <378D591D.9BF2B835@ccis.adisys.com.au>

Anneke Treep wrote:

> Hi all,
>
> I am trying to do the following:

> <snip so my amil tool will let me send>

> SELECT (additonal-data)
> FROM additional-tables
> WHERE (person.person-id, session.session-id, 2) IN (
> SELECT (session.id, person.id, session.type)
> FROM (base-tables)
> WHERE (base-restriction)
> ) base_alias
> UNION
> SELECT (other additional-data)
> FROM additional-tables
> WHERE (person.person-id, session.session-id, 3) IN
> base_alias
> ...
> <snip>
> Any suggestions?
> And, is it easy to read the person.id, session.id and session-type along
> with the additional-data?
> Many thanks in advance!
>
> Anneke Treep, Echelon BV

Maybe you don't need to UNION at all. Judging from your pseudo-code you are always selecting from the same tables. So another possibility is to use decode to select the appropriate column from the additional_table based on the session-type.

select decode(session_type, 2, ad.col_1, 3, ad.col_6),

          decode(session_type, 2, ad.col_2, 3, ad.col_7),
           .... repeat for each column ....

from base_table, additional_tables ad
where (base-restriction)
and (join-base-table-to-additional-tables)

HTH
Geoff Received on Wed Jul 14 1999 - 22:44:29 CDT