Hello

I have 2 tables: active items and history of the items. The 2 tables have almost the same structure except that the history has an extra field (a date field).

I want to join these 2 tables with an UNION query.
Is there a way to add the extra field from the history table into the query?
Is there a way to add an extra field to the query so I can see the source of the record (from which table the record comes from)?

Thanks,
Beni.

Beni,

> I have 2 tables: active items and history of the items. The 2 tables have almost the same structure except that the history has an extra field (a date field).
>
> I want to join these 2 tables with an UNION query.
> Is there a way to add the extra field from the history table into the query?
> Is there a way to add an extra field to the query so I can see the source of the record (from which table the record comes from)?

Yes, that should be no problem.

Use something like this:

select 'items' as tablename, field1, field2, CURRENT_DATE as fieldDate
from items

union all

select 'history' as tablename, field1, field2, field3
from history

where field3 is the date field of the history table.

Uli

Great!

It's working! I can't use NULL in place of CURRENT_DATE but I did manage to find a workaround

Beni,

<<
I can't use NULL in place of CURRENT_DATE but I did manage to find a
workaround
>>

Sure you can

 cast(null as Date) as MyDate

Uli

Beni


>Is there a way to add the extra field from the history table into the query?
>Is there a way to add an extra field to the query so I can see the source of the record (from which table the record comes from)?

Its a very similar answer for both. Rather than the field name simply have CAST(NULL AS DATE) AS xx for the missing date column in the active items table where xx is the name of the column in the history table and 'Active' AS List in the active table and 'History' AS List for the history table.

Your biggest problem is making sure the columns are listed in the right place.

Roy Lambert [Team Elevate]

Thanks for your help. I did manage to get the right results.