Adding Custom Columns to Union Table - Oracle SQL

Question

I'm dealing with the following union between two different tables in an Oracle Database.

the first query refers to vessels that are part of the current fleet of the payer_no. The second query refers to all vessels transacted with at some time.

If an item appears only in the first query, I want a custom column that says "current fleet" and if an item only appears in the second query, I want a column that says "past fleet."

Output would look like this:

enter image description here

What should happen if an item (whatever that means) appears in BOTH members of the UNION? (On a separate note - especially if you expect the UNION to be disjoint anyway, and the members have no duplicate rows or you don't care if they have duplicate rows - use UNION ALL instead of UNION for better performance). — user5683823
– user5683823, Commented Dec 4, 2017 at 18:49
if an item appears in both, it needs to be tagged to the first query ("current fleet") — David Neiderauer
– David Neiderauer, Commented Dec 7, 2017 at 3:25

user5683823 · Accepted Answer · 2017-12-04 18:53:37Z

2

Pending clarification of what should happen if an "item" appears in both members of the UNION:

The usual way to do what you need is to add a column with hard-coded values and with the same name to both members of the union. It doesn't matter if one or both members is/are the result of a join, or whatever other simple or complicated computation.

select col_1, col_2, ... , col_n, 'Current Fleet' as status
from   ( big join or whatever here; do prefix columns with table aliases in a join! )
UNION    --  Or UNION ALL  !
select col_1, col_2, ... , col_n, 'Past Fleet'    as status
from   .........

answered Dec 4, 2017 at 18:53

user5683823

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Adding Custom Columns to Union Table - Oracle SQL

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related