OpenSQL Enhancements

SELECT * FROM sflight
  INTO TABLE @DATA(lt_sflight).

SELECT carrid, connid, fldate FROM sflight
  INTO TABLE @DATA(lt_sflight).

SELECT carrid, connid, fldate FROM sflight
  WHERE carrid IN @s_carrid
  INTO TABLE @DATA(lt_sflight).

SELECT carrid AS carrier,
       connid AS connection,
       fldate AS flight_date
       ' ' AS status_flag
  FROM sflight
  WHERE carrid IN @s_carrid
  INTO TABLE @DATA(lt_sflight).

SELECT SINGLE FROM @lt_flights AS flights
  FIELDS carrier_id, connection_id
  WHERE flight_date = @sy-datum
  AND   city_from = 'FRANKFURT'
  INTO @DATA(lt_todays_flight).

SELECT SINGLE CONCAT( vorna, nachn ) AS name_concat,
              CONCAT_WITH_SPACE( vorna, nachn, 1 ) AS nachn_concat_with_space
              LENGTH( nachn ) AS nachn_length,
              LEFT( nachn, 2 ) AS nachn_left,
              LOWER( nachn ) AS nachn_lower,
              UPPER( nachn ) AS nachn_upper,
              RIGHT( nachn, 2 ) AS nachn_right,
              SUBSTRING( nachn, 2, 2 ) AS nachn_substring,
              REPLACE( nachn, 'ß' , 'ss' ) AS nachn_replace,
  FROM pa0002 INTO @DATA(ls_pa0002)
  WHERE pernr = @iv_pernr
  AND   begda <= @sy-datum
  AND   endda >= @sy-datum.

DATA(lv_search_string) = 'IRon'.
DATA(lv_search_text) = |%{ to_upper( lv_search_string ) }%|.
SELECT * FROM t100
  WHERE upper( text ) LIKE @lv_search_text
  INTO TABLE @DATA(lt_t100).

SELECT SINGLE DATS_IS_VALID( begda ) AS valid,
              DATS_DAYS_BETWEEN( begda, endda ) AS days_between,
              DATS_ADD_DAYS( begda, 100 ) AS add_days,
              DATS_ADD_MONTH( begda, -1 ) AS add_month
  FROM hrp1001
  INTO @DATA(ls_hrp1001).

SELECT mandt, cccategory,
       CASE cccategory
         WHEN 'C' THEN 'Customizing'
         WHEN 'P' THEN 'Production'
         WHEN 'T' THEN 'Testing'
         ELSE 'Something Else'
       END AS group
  FROM t000
  INTO @DATA(ls_t000).

SELECT carrid, connid, fldate, seatsocc_b, seatsocc,
       CASE
         WHEN seatsocc_b < 10 AND seatsocc < 10 THEN 'Both less than 10'
         WHEN seatsocc_b ≥ 10 AND seatsocc ≥ 10 THEN 'Both more than 10'
         ELSE 'Something Else'
       END AS group
  FROM sflight
  INTO @DATA(ls_sflight).

SELECT carrid,
       connid,
       fltime + 90 AS total_time,
       DIVISION( fltime, 60, 2 ) AS flight_time
  FROM spfli
  WHERE carrid IN @s_carrid
  INTO TABLE @DATA(lt_sflight).

SELECT FROM /dmo/flight
  FIELDS
    carrier_id AS carrier,
    connection_id AS connection,
    flight_date AS date,
    price,
    COUNT(*) OVER( PARTITION BY carrier_id ) AS cnt_car,
    COUNT(*) OVER( PARTITION BY carrier_id, connection_id ) AS cnt_conn,
    AVG( price ) OVER( PARTITION BY carrier_id, connection_id ) AS avg_price,
    MIN( price ) OVER( PARTITION BY carrier_id, connection_id ) AS min_price,
    MAX( price ) OVER( PARTITION BY carrier_id, connection_id ) AS max_price,
    SUM( price ) OVER( PARTITION BY carrier_id, connection_id ) AS total_price,
    DIVISION( 100 * price, SUM( price ) OVER( PARTITION BY carrier_id, connection_id ), 2 ) AS percentage
  ORDER BY carrier_id, connection_id, flight_date
  INTO TABLE @DATA(lt_dmo_flight).

SELECT FROM /dmo/flight
  FIELDS
    carrier_id, seats_occupied,
    AVG( seats_occupied ) OVER( PARTITION BY carrier_id ) AS avg_seats_occupied
  INTO TABLE @DATA(lt_dmo_flight).

SELECT FROM /dmo/flight
  FIELDS
    carrier_id, seats_occupied,
    AVG( seats_occupied ) OVER( ORDER BY seats_occupied
                                ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW )
                          AS avg_seats_occupied
  INTO TABLE @DATA(lt_dmo_flight).

SELECT FROM /dmo/flight
  FIELDS
    carrier_id, flight_date, seats_occupied,
    AVG( seats_occupied ) OVER( PARTITION BY carrier_id ORDER BY flight_date
                                ROWS BETWEEN 2 PRECEDING AND CURRENT ROW )
                           AS avg_seats_occupied
  INTO TABLE @DATA(lt_dmo_flight).

SELECT carrier_id, connection_id,
       SUM( seats_occupied ) AS travellers
  FROM /dmo/flight
  GROUP BY carrier_id, connection_id
  ORDER BY carrier_id, connection_id
  INTO TABLE @DATA(lt_travels_by_connection).

SELECT FROM /dmo/flight
  FIELDS
    carrier_id, connection_id, flight_date, price,
    AVG( price ) OVER( PARTITION BY carrier_id, connection_id ) AS average_price
  ORDER BY carrier_id, connection_id, flight_date
  INTO TABLE @DATA(lt_window).

SELECT carrid,
       SUM( price ) AS total_price
  FROM sflight
  GROUP BY carrid
  INTO TABLE @DATA(lt_sflight).

SELECT carrid, seatsocc_f, seatsocc_b, seatsocc,
       seatsocc_f + seatsocc_b + seatsocc AS occupied,
       COUNT( * ) AS count
  FROM sflight
  GROUP BY seatsocc_f + seatsocc_b + seatsocc
  HAVING COUNT( * ) > 10
  ORDER BY occupied
  INTO TABLE @DATA(lt_sflight).

SELECT FROM /dmo/flight
  FIELDS
    carrier_id, connection_id, seats_occupied,
    ROW_NUMBER( ) OVER( PARTITION BY carrier_id ORDER BY seats_occupied ) AS so_row,
    DENSE_RANK( ) OVER( PARTITION BY carrier_id ORDER BY flight_date ) AS fldate_row
  INTO TABLE @DATA(lt_dmo_flight).

SELECT FROM /dmo/flight
  FIELDS
    carrier_id, connection_id, seats_occupied,
    RANK( ) OVER( PARTITION BY carrier_id ORDER BY connection_id ) AS rank,
    DENSE_RANK( ) OVER( PARTITION BY carrier_id ORDER BY connection_id ) AS dense_rank
  INTO TABLE @DATA(lt_dmo_flight).

SELECT FROM /dmo/flight
  FIELDS
    carrier_id, connection_id, seats_occupied,
    NTILE( 3 ) OVER( PARTITION BY carrier_id ORDER BY connection_id ) AS group_number
  INTO TABLE @DATA(lt_dmo_flight).

SELECT FROM /dmo/flight
  FIELDS
    carrier_id, connection_id, seats_occupied,
    FIRST_VALUE( seats_occupied ) OVER( PARTITION BY carrier_id ORDER BY connection_id ) AS seats_first,
    LAST_VALUE( seats_occupied ) OVER( PARTITION BY carrier_id ORDER BY connection_id ) AS seats_last,
    LAST_VALUE( seats_occupied ) OVER( PARTITION BY carrier_id ORDER BY connection_id
                                       ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING ) AS true_seats_last
  INTO TABLE @DATA(lt_dmo_flight).

SELECT FROM /dmo/flight
  FIELDS
    carrier_id, connection_id, seats_occupied,
    LEAD( seats_occupied ) OVER( PARTITION BY carrier_id ORDER BY connection_id ) AS lead_seats,
    LAG( seats_occupied ) OVER( PARTITION BY carrier_id ORDER BY connection_id ) AS lag_seats
  INTO TABLE @DATA(lt_dmo_flight).

SELECT * FROM sflight
  WHERE ( seatsmax - seatsocc ) > 10
  INTO TABLE @DATA(lt_sflight).

SELECT * FROM sflight
  WHERE connid = @( lcl_local_class->get_connid( ) )
  INTO TABLE @DATA(lt_sflight).

SELECT * FROM sflight
  WHERE price IS NOT INITIAL
  AND   seatsmax IS NOT INITIAL
  INTO TABLE @DATA(lt_sflight).

DATA(lv_city_from) = 'BERLIN'.
SELECT a~carrname, b~connid FROM scarr AS a
  INNER JOIN spfli AS b ON  b~carrid = a~carrid
                        AND b~cityfrom = @lv_city_from
  INTO TABLE @DATA(lt_sflight).

SELECT * FROM /dmo/carrier
  WHERE carrier_id IN @lr_carriers
  INTO TABLE @DATA(lt_carrier).
IF sy-subrc = 0.
  SELECT a~carrier_id,
         connection_id,
         SUM( seats_occupied ) AS travellers
    FROM /dmo/flight AS a
    INNER JOIN @lt_carrier AS n ON b~carrier_id = a~carrier_id
    GROUP BY a~carrier_id, connection_id
    ORDER BYv a~carrier_id, connection_id
    INTO TABLE @DATA(lt_travels_by_connection).
ENDIF.

SELECT schedule~carrid, schedule~connid, schedule~fldate,
       flight~cityfrom, flight~cityto
  FROM sflight AS schedule
  INNER JOIN spfli AS flight
    ON  schedule~carrid = flight~carrid
    AND schedule~connid = flight~connid
    WHERE flight~cityfrom = 'FRANKFURT'
UNION ALL
SELECT schedule~carrid, schedule~connid, schedule~fldate,
       flight~cityfrom, flight~cityto
  FROM sflight AS schedule
  INNER JOIN spfli AS flight
    ON  schedule~carrid = flight~carrid
    AND schedule~connid = flight~connid
  WHERE flight~cityfrom = 'SAN FRANCISCO'
INTO TABLE @DATA(lt_sflight).

"1. Daten aus einer Datenbanktabelle selektieren und internte Tabelle bestücken
SELECT schedule~carrid AS carrier_id,
       schedule~connid AS connection_id,
       schedule~fldate AS flight_date,
       flight~cityfrom AS city_from,
       flight~cityto   AS city_to
  FROM sflight AS schedule
INNER JOIN spfli AS flight
    ON  schedule~carrid = flight~carrid
    AND schedule~connid = flight~connid
  INTO TABLE @DATA(lt_flights).
 
"2. Daten aus der internen Tabelle selektieren
SELECT carrier_id, connection_id, flight_date
  FROM @lt_flights AS flights
  WHERE city_from = 'FRANKFURT'
  INTO TABLE @DATA(lt_new_flights).

WITH
  +cities AS ( SELECT cityfrom AS city FROM spfli WHERE carrid = 'LH'
               UNION DISTINCT
               SELECT cityto AS city FROM spfli WHERE carrid = 'LH' )
  SELECT * FROM sgeocity
    WHERE city IN ( SELECT city FROM +cities )
    INTO TABLE @DATA(result).

SELECT * FROM sflight
  USING CLIENT '100'
  WHERE carrid IN @s_carrid
  INTO TABLE @DATA(lt_sflight).

SELECT * FROM sflight
  USING ALL CLIENTS
  WHERE carrid IN @s_carrid
  INTO TABLE @DATA(lt_sflight).