Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB SQL » View Thread |
Messages 1 to 6 of 6 total |
UNION Question |
Mon, Mar 25 2013 9:51 AM | Permanent Link |
Beni | 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. |
Mon, Mar 25 2013 10:24 AM | Permanent Link |
Uli Becker | 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 |
Mon, Mar 25 2013 10:56 AM | Permanent Link |
Beni | Great!
It's working! I can't use NULL in place of CURRENT_DATE but I did manage to find a workaround |
Mon, Mar 25 2013 12:05 PM | Permanent Link |
Uli Becker | 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 |
Mon, Mar 25 2013 12:14 PM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | 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] |
Mon, Mar 25 2013 5:15 PM | Permanent Link |
Beni | Thanks for your help. I did manage to get the right results.
|
This web page was last updated on Saturday, April 27, 2024 at 08:52 PM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |