ABAP Advanced Open SQL: WITH, Window Functions and More

WITH
  +customers AS (
    SELECT kunnr, name1, land1
    FROM kna1
    WHERE land1 = 'DE'
  )
SELECT * FROM +customers
  INTO TABLE @DATA(lt_german_customers).

WITH
  +orders AS (
    SELECT vbeln, kunnr, netwr, erdat
    FROM vbak
    WHERE erdat >= '20240101'
  ),
  +customers AS (
    SELECT kunnr, name1
    FROM kna1
  )
SELECT o~vbeln, o~netwr, c~name1
  FROM +orders AS o
  INNER JOIN +customers AS c ON o~kunnr = c~kunnr
  INTO TABLE @DATA(lt_orders_with_names).

WITH
  +order_totals AS (
    SELECT kunnr,
           COUNT(*) AS order_count,
           SUM( netwr ) AS total_value
    FROM vbak
    GROUP BY kunnr
  )
SELECT c~kunnr, c~name1,
       t~order_count, t~total_value
  FROM kna1 AS c
  INNER JOIN +order_totals AS t ON c~kunnr = t~kunnr
  WHERE t~total_value > 100000
  INTO TABLE @DATA(lt_top_customers).

WITH
  +hierarchy( node, parent, level ) AS (
    " Start nodes
    SELECT org_id, parent_id, 1 AS level
      FROM zorg_structure
      WHERE parent_id IS INITIAL
    UNION ALL
    " Recursive part
    SELECT child~org_id, child~parent_id, parent~level + 1
      FROM zorg_structure AS child
      INNER JOIN +hierarchy AS parent ON child~parent_id = parent~node
  )
SELECT * FROM +hierarchy
  INTO TABLE @DATA(lt_org_tree).

SELECT kunnr, vbeln, netwr,
       ROW_NUMBER( ) OVER( PARTITION BY kunnr ORDER BY netwr DESC ) AS rank
  FROM vbak
  INTO TABLE @DATA(lt_ranked_orders).

" Only top 3 per customer
SELECT * FROM @lt_ranked_orders AS t
  WHERE t~rank <= 3
  INTO TABLE @DATA(lt_top3).

SELECT matnr, werks, lgort, labst,
       RANK( ) OVER( PARTITION BY matnr ORDER BY labst DESC ) AS rank,
       DENSE_RANK( ) OVER( PARTITION BY matnr ORDER BY labst DESC ) AS dense_rank
  FROM mard
  INTO TABLE @DATA(lt_stock_ranked).

" RANK: 1, 2, 2, 4 (gaps for equal values)
" DENSE_RANK: 1, 2, 2, 3 (no gaps)

SELECT vbeln, erdat, netwr,
       SUM( netwr ) OVER( ORDER BY erdat ) AS running_total
  FROM vbak
  WHERE kunnr = '0000001000'
  INTO TABLE @DATA(lt_running_total).

SELECT vbeln, erdat, netwr,
       LAG( netwr, 1 ) OVER( ORDER BY erdat ) AS prev_order_value,
       LEAD( netwr, 1 ) OVER( ORDER BY erdat ) AS next_order_value
  FROM vbak
  WHERE kunnr = '0000001000'
  ORDER BY erdat
  INTO TABLE @DATA(lt_with_neighbors).

" Calculate change from previous month
LOOP AT lt_with_neighbors INTO DATA(ls_order).
  IF ls_order-prev_order_value IS NOT INITIAL.
    DATA(lv_change) = ls_order-netwr - ls_order-prev_order_value.
  ENDIF.
ENDLOOP.

SELECT kunnr, vbeln, erdat, netwr,
       FIRST_VALUE( netwr ) OVER(
         PARTITION BY kunnr ORDER BY erdat
       ) AS first_order_value,
       LAST_VALUE( netwr ) OVER(
         PARTITION BY kunnr ORDER BY erdat
         ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
       ) AS last_order_value
  FROM vbak
  INTO TABLE @DATA(lt_first_last).

SELECT kunnr, vbeln, netwr,
       AVG( netwr ) OVER( PARTITION BY kunnr ) AS avg_order_value,
       MIN( netwr ) OVER( PARTITION BY kunnr ) AS min_order_value,
       MAX( netwr ) OVER( PARTITION BY kunnr ) AS max_order_value
  FROM vbak
  INTO TABLE @DATA(lt_with_stats).

" Compare with average
LOOP AT lt_with_stats INTO DATA(ls_order).
  IF ls_order-netwr > ls_order-avg_order_value.
    WRITE: / ls_order-vbeln, 'Above average'.
  ENDIF.
ENDLOOP.

SELECT vbeln, netwr,
       CASE
         WHEN netwr >= 10000 THEN 'HIGH'
         WHEN netwr >= 1000 THEN 'MEDIUM'
         ELSE 'LOW'
       END AS value_category
  FROM vbak
  INTO TABLE @DATA(lt_categorized).

SELECT vbeln, auart,
       CASE auart
         WHEN 'TA' THEN 'Standard Order'
         WHEN 'SO' THEN 'Rush Order'
         WHEN 'RE' THEN 'Returns'
         ELSE 'Other'
       END AS order_type_text
  FROM vbak
  INTO TABLE @DATA(lt_with_text).

SELECT kunnr,
       COUNT(*) AS total_orders,
       SUM( CASE WHEN netwr >= 10000 THEN 1 ELSE 0 END ) AS high_value_count,
       SUM( CASE WHEN netwr < 1000 THEN 1 ELSE 0 END ) AS low_value_count
  FROM vbak
  GROUP BY kunnr
  INTO TABLE @DATA(lt_order_analysis).

SELECT vbeln,
       'EUR' AS currency,
       2024 AS year,
       netwr * 1.19 AS gross_value
  FROM vbak
  INTO TABLE @DATA(lt_with_literals).

SELECT matnr,
       labst,
       insme,
       labst - insme AS available_stock,
       labst + insme AS total_quantity,
       CASE WHEN labst > 0
         THEN insme * 100 / labst
         ELSE 0
       END AS inspection_percentage
  FROM mard
  INTO TABLE @DATA(lt_stock_calc).

SELECT kunnr,
       CONCAT( name1, CONCAT( ' (', CONCAT( ort01, ')' ) ) ) AS display_name,
       LENGTH( name1 ) AS name_length,
       LEFT( kunnr, 4 ) AS prefix
  FROM kna1
  INTO TABLE @DATA(lt_formatted).

SELECT vbeln,
       SUBSTRING( vbeln, 1, 4 ) AS prefix,
       REPLACE( vbeln, '00', 'XX' ) AS replaced
  FROM vbak
  INTO TABLE @DATA(lt_string_ops).

SELECT name1,
       UPPER( name1 ) AS upper_name,
       LOWER( name1 ) AS lower_name,
       LTRIM( name1, ' ' ) AS left_trimmed,
       RTRIM( name1, ' ' ) AS right_trimmed
  FROM kna1
  INTO TABLE @DATA(lt_case_converted).

SELECT vbeln, erdat,
       DATS_DAYS_BETWEEN( erdat, @sy-datum ) AS days_ago,
       DATS_ADD_DAYS( erdat, 30 ) AS plus_30_days,
       DATS_ADD_MONTHS( erdat, 1 ) AS plus_1_month
  FROM vbak
  INTO TABLE @DATA(lt_with_dates).

SELECT vbeln, erdat,
       EXTRACT( YEAR FROM erdat ) AS year,
       EXTRACT( MONTH FROM erdat ) AS month,
       EXTRACT( DAY FROM erdat ) AS day
  FROM vbak
  INTO TABLE @DATA(lt_date_parts).

" Grouping by year/month
SELECT EXTRACT( YEAR FROM erdat ) AS year,
       EXTRACT( MONTH FROM erdat ) AS month,
       COUNT(*) AS order_count,
       SUM( netwr ) AS total_value
  FROM vbak
  GROUP BY EXTRACT( YEAR FROM erdat ), EXTRACT( MONTH FROM erdat )
  INTO TABLE @DATA(lt_monthly).

SELECT vbeln,
       CAST( netwr AS CHAR( 20 ) ) AS netwr_string,
       CAST( '12345' AS INT4 ) AS number_value,
       CAST( erdat AS CHAR( 8 ) ) AS date_string
  FROM vbak
  INTO TABLE @DATA(lt_casted).

SELECT kunnr, name1,
       COALESCE( name2, '' ) AS name2_safe,
       COALESCE( ort01, 'Unknown' ) AS city
  FROM kna1
  INTO TABLE @DATA(lt_with_defaults).

SELECT c~kunnr, y~year
  FROM kna1 AS c
  CROSS JOIN ( SELECT DISTINCT EXTRACT( YEAR FROM erdat ) AS year FROM vbak ) AS y
  INTO TABLE @DATA(lt_customer_years).

SELECT c~kunnr, c~name1,
       o~last_order, o~last_value
  FROM kna1 AS c
  LEFT OUTER JOIN (
    SELECT kunnr,
           MAX( erdat ) AS last_order,
           MAX( netwr ) AS last_value
    FROM vbak
    GROUP BY kunnr
  ) AS o ON c~kunnr = o~kunnr
  INTO TABLE @DATA(lt_with_last_order).

SELECT vbeln, kunnr, netwr,
       ( SELECT name1 FROM kna1 WHERE kunnr = vbak~kunnr ) AS customer_name
  FROM vbak
  INTO TABLE @DATA(lt_with_name).

SELECT kunnr, name1
  FROM kna1 AS c
  WHERE EXISTS (
    SELECT * FROM vbak WHERE kunnr = c~kunnr AND erdat >= '20240101'
  )
  INTO TABLE @DATA(lt_active_customers).

SELECT * FROM kna1
  WHERE kunnr IN ( SELECT DISTINCT kunnr FROM vbak WHERE netwr > 50000 )
  INTO TABLE @DATA(lt_high_value_customers).

" 1. CTE instead of multiple identical subqueries
WITH
  +high_value AS (
    SELECT kunnr FROM vbak WHERE netwr > 50000
  )
SELECT * FROM kna1
  WHERE kunnr IN ( SELECT kunnr FROM +high_value )
  INTO TABLE @lt_result.


" 2. Window Functions instead of correlated subqueries
" SLOW:
SELECT vbeln, netwr,
       ( SELECT SUM( netwr ) FROM vbak AS inner WHERE inner~kunnr = outer~kunnr )
  FROM vbak AS outer INTO TABLE @lt_slow.

" FASTER:
SELECT vbeln, netwr,
       SUM( netwr ) OVER( PARTITION BY kunnr ) AS customer_total
  FROM vbak INTO TABLE @lt_fast.


" 3. Filter early with WHERE
WITH
  +filtered AS (
    SELECT * FROM vbak WHERE erdat >= '20240101'  " Filter here
  )
SELECT * FROM +filtered ...