Re: SQL exception join: Left side now needs data combined from two tables

[Joe Pluta <joepluta@xxxxxxxxxxxxxxxxx>]

CTE (common table expression)?

Something like

with t1 as (select * from A1 union
            select * from A2)
select * from EE exception join T1
                 on EEPOL# = AAPOL#


Because of the Select *, the fields have to be the same in both fields.

Not sure this will work, but it's worth a shot.

Joe


> I have a query that uses an exception join.  I.e.
>   select * from EE exception join A1 on EEPOL# = AAPOL#
>
>  Some of the data in A1 is now being split off into a new table A2, so data
> that used to be in A1 but is now in A2 is being returned as a result in this
> query.  I need the right side of this join to have the rows from both A1 &
> A2.  Pseudo-code:
>   select * from EE exception join (A1||A2) on EEPOL# = A1POL#
>
> I know I could use a temporary file to combine A1 & A2, use the temp file on
> the right side, and call it done, but I'm trying to learn whether SQL has a
> way to handle this.  I thought of UNION, but don't think this will work for
> an exception join.   I tried:
>
>   select * from EE exception join
>           (select * from A1 union
>            select * from A2       ) on EEPOL# = AAPOL#
>
> but no good.
>
> Any ideas would be greatly appreciated.
>
> TIA,
> Dan
>