SAP CDS Views: Merging Datasets with UNION and UNION ALL

SAP ABAP CDS View: UNION In SAP ABAP CDS Views, the UNION operator is used to combine the results of two or more SELECT queries into a single result set. This is useful when you need to merge data from multiple tables or views that have similar structures.

Key Points About UNION in CDS Views

1. Combines Data from multiple sources with the same structure.
2. Removes Duplicates by default (similar to UNION DISTINCT in SQL).
3. Use UNION ALL if you want to keep duplicates.
4. Column Structure of all UNIONed queries must be the same.
5. You can include additional logic like calculations, filters, and case conditions.

Basic Example of UNION in CDS Views

The example below merges flight data from two tables (sflight and spfli).

@AbapCatalog.sqlViewName: 'ZFLIGHT_UNION'
@EndUserText.label: 'Union Example for Flight Data'
define view ZFLIGHT_UNION as
    select from sflight {
        carrid,
        connid,
        fldate as travel_date,
        price,
        'SFLIGHT' as source_table
    }
union
    select from spfli {
        carrid,
        connid,
        null as travel_date,  // Placeholder for missing field
        distance as price,     // Mapping distance to price field
        'SPFLI' as source_table
    };

Explanation

• The first SELECT fetches flight data (sflight) with fields: carrid, connid, fldate, and price.
• The second SELECT retrieves similar fields from spfli, but:
• fldate is not available in spfli, so we use NULL.
• distance is used instead of price for alignment.
• A constant value ‘SFLIGHT’ and ‘SPFLI’ is added to identify the source table.
• UNION ensures that duplicate records are eliminated.

Using UNION ALL (Retaining Duplicates)

If you want to keep duplicates, use UNION ALL instead of UNION.

define view ZFLIGHT_UNION_ALL as
    select from sflight {
        carrid,
        connid,
        fldate as travel_date,
        price,
        'SFLIGHT' as source_table
    }
union all
    select from spfli {
        carrid,
        connid,
        null as travel_date,
        distance as price,
        'SPFLI' as source_table
    };

🔹 Difference: This keeps duplicate records from both tables.

Adding a WHERE Condition in UNION

You can filter data separately in each SELECT statement.

define view ZFLIGHT_UNION_FILTERED as
    select from sflight
    where price > 500 {  // Flights with price > 500
        carrid,
        connid,
        fldate as travel_date,
        price,
        'SFLIGHT' as source_table
    }
union
    select from spfli
    where distance > 1000 {  // Routes with distance > 1000
        carrid,
        connid,
        null as travel_date,
        distance as price,
        'SPFLI' as source_table
    };

🔹 Each SELECT has different filters applied before merging results.

Using UNION with Calculated Fields

You can apply calculations and transformations before merging datasets.

define view ZFLIGHT_UNION_CALC as
    select from sflight {
        carrid,
        connid,
        fldate as travel_date,
        price * 1.1 as adjusted_price,  // Apply a 10% increase
        'SFLIGHT' as source_table
    }
union
    select from spfli {
        carrid,
        connid,
        null as travel_date,
        distance / 10 as adjusted_price,  // Convert distance into price
        'SPFLI' as source_table
    };

🔹 Business logic applied:

• Increased price by 10% in sflight.
• Converted distance to price in spfli.

Summary

📌 When to Use?

• Use UNION when duplicate records should be eliminated.
• Use UNION ALL when performance is critical and duplicates are acceptable.

Final Thoughts

• The UNION operator in SAP ABAP CDS Views is an efficient way to merge data from multiple tables/views.
• Performance Tip: UNION ALL is faster because it skips duplicate removal.
• You can apply filters, transformations, and calculations before merging datasets.