ABAP CDS View Nested SELECT: Subqueries & Associations

In SAP ABAP CDS Views, a nested SELECT or subquery is a way to include a SELECT statement inside another SELECT statement. This is often used to fetch related data that is dependent on a field from the outer query, allowing you to retrieve aggregated or filtered values that are not directly available in the main table.

While CDS Views do not allow direct usage of traditional SQL subqueries (like in ABAP), nested queries or subqueries can be accomplished through certain techniques like association and joins.

1. Using Associations for Nested Queries in CDS Views

An association is a relationship between two entities or tables that can be used in CDS Views to simulate nested SELECTs or subqueries.

Example: Using an Association to Simulate Nested Select

define view ZFLIGHT_DETAIL as select from sflight {
    key carrid,
    key connid,
    fldate,
    price,
    // Create an association to simulate a subquery
    association to zflight_detail as _flight_detail on $projection.carrid = _flight_detail.carrid
}

Explanation:

• In this example, association to is used to establish a relationship between the main sflight table and the zflight_detail table.
• This approach avoids the need for a nested SELECT by directly joining related data through the association.

2. Using Subqueries in SELECT Expressions (Simulated)

While you cannot use a traditional subquery inside a SELECT clause in a CDS view like you might in SQL, you can still achieve similar results by leveraging aggregations or scalar subqueries in SELECT expressions.

Example: Using Scalar Subquery in CDS View Expression

You can use scalar subqueries in expressions (e.g., SELECT fields) to retrieve aggregated or filtered values from other tables.

define view ZFLIGHT_SUBQUERY as select from sflight {
    key carrid,
    key connid,
    fldate,
    price,
    // Scalar subquery to get the total price for the carrier
    ( select sum(price) from sflight as s2 where s2.carrid = $projection.carrid ) as total_price
}

Explanation:

• In this example, the scalar subquery inside the SELECT clause is used to retrieve the total price for each carrid by summing up the price from the sflight table.
• The subquery calculates the sum of price for each carrier (carrid) and returns it as a field total_price in the result.

3. Using JOIN for Nested Select Logic

Another approach to simulate nested SELECTs is by using joins to combine data from multiple tables and eliminate the need for nested queries. This is often the most common approach in CDS Views.

Example: Using JOINs to Fetch Related Data

define view ZFLIGHT_JOIN as select from sflight as f
    inner join sbook as b on f.carrid = b.carrid and f.connid = b.connid
{
    f.carrid,
    f.connid,
    f.fldate,
    f.price,
    b.booking_date
}

Explanation:

• Here, a JOIN is used to fetch related data from the sflight and sbook tables, avoiding the need for a subquery by directly joining the tables on common fields (carrid, connid).
• The result includes both the flight details and the booking date in a single query.

4. Using Common Table Expressions (CTEs)

In ABAP CDS views, CTEs (Common Table Expressions) can be used to simplify complex queries. While CTEs are not exactly “nested selects,” they can be used to structure your query logically and break it into reusable parts, making it easier to reference and manage.

Example: Using CTEs in CDS Views

define view ZFLIGHT_CTE as select from sflight {
    key carrid,
    key connid,
    fldate,
    price
}
with total_flights as (
    select carrid, count(*) as flight_count from sflight group by carrid
)
select f.*, t.flight_count
from sflight as f
inner join total_flights as t on f.carrid = t.carrid

Explanation:

• The total_flights CTE is defined as a temporary result set containing the count of flights for each carrier (carrid).
• The main SELECT statement joins the sflight table with this CTE to add the flight count to the result set.

5. Using Subqueries in CASE Expressions

Subqueries can also be used in a CASE expression for conditional logic.

Example: Using a Subquery in CASE Expression

define view ZFLIGHT_CASE_SUBQUERY as select from sflight {
    key carrid,
    key connid,
    fldate,
    price,
    // Use a subquery inside a CASE statement for conditional logic
    case
        when ( select count(*) from sbook where sbook.carrid = $projection.carrid and sbook.connid = $projection.connid ) > 0 then 'Booked'
        else 'Available'
    end as flight_status
}

Explanation:

• This example checks whether there is a booking for the flight (carrid, connid combination) by using a subquery inside the CASE expression.
• If the count from the sbook table is greater than zero, it returns Booked; otherwise, it returns Available.

Conclusion

In SAP ABAP CDS Views, while traditional nested SELECTs are not directly supported, you can still achieve similar functionality through the following methods:

1. Associations: These allow you to simulate subqueries by establishing relationships between different tables or entities.
2. Scalar Subqueries: You can use scalar subqueries in expressions to fetch aggregated or conditional values.
3. Joins: Use JOIN operations to combine related data from multiple tables in a more efficient, set-based way.
4. Common Table Expressions (CTEs): Break down complex queries into simpler, reusable parts using CTEs.
5. CASE Expressions: Combine conditional logic with subqueries to simulate nested SELECT logic.

Would you like to explore any of these techniques in more detail or have further examples for your use case? Let me know! 😊