(ABAP 코드) ABAP SQL QUERY

김병철 멘토님께서 주신 코드 (2021-06-12)

REPORT Z0612_00_HEOM.



DATA LT_SFLIGHT LIKE TABLE OF SFLIGHT WITH HEADER LINE.

*--------------------------------------------------------------------*
* SUB QUERY- IN EQ
* LIKE
*--------------------------------------------------------------------*
SELECT *
  INTO TABLE LT_SFLIGHT
  FROM SFLIGHT
 WHERE CARRID IN ( SELECT CARRID
                      FROM SFLIGHT
                     WHERE CARRID LIKE 'A%' )
   AND FLDATE EQ ( SELECT MAX( FLDATE )
                     FROM SFLIGHT
                    WHERE CONNID < 100
                       OR CONNID BETWEEN 701 AND 800 ).


*--------------------------------------------------------------------*
* APPENDING
* LEFT OUTER JOIN
*--------------------------------------------------------------------*

APPEND INITIAL LINE TO LT_SFLIGHT.
SELECT A~CARRID B~CONNID B~FLDATE
APPENDING CORRESPONDING FIELDS OF TABLE LT_SFLIGHT
FROM SCARR AS A LEFT OUTER JOIN SFLIGHT AS B ON
                A~CARRID = B~CARRID
WHERE A~CARRID EQ 'AC'.


*--------------------------------------------------------------------*
* APPENDING
* GROUP BY / HAVING
*--------------------------------------------------------------------*

APPEND INITIAL LINE TO LT_SFLIGHT.
SELECT CARRID CONNID PAYMENTSUM
  UP TO 5 ROWS
APPENDING CORRESPONDING FIELDS OF TABLE LT_SFLIGHT
FROM SFLIGHT
WHERE CARRID NOT LIKE 'A_'
  GROUP BY CARRID CONNID PAYMENTSUM
 HAVING PAYMENTSUM > ( SELECT AVG( PAYMENTSUM )
                          FROM SFLIGHT )
  ORDER BY PAYMENTSUM DESCENDING.



*--------------------------------------------------------------------*
* COUNT
*--------------------------------------------------------------------*

APPEND INITIAL LINE TO LT_SFLIGHT.
SELECT CARRID COUNT( * ) AS PRICE
APPENDING CORRESPONDING FIELDS OF TABLE LT_SFLIGHT
FROM SFLIGHT
  GROUP BY CARRID .


*--------------------------------------------------------------------*
* SUM
*--------------------------------------------------------------------*

APPEND INITIAL LINE TO LT_SFLIGHT.
SELECT CARRID SUM( SEATSMAX ) AS SEATSMAX
  APPENDING CORRESPONDING FIELDS OF TABLE LT_SFLIGHT
       FROM SFLIGHT
  GROUP BY CARRID.

 
IF LT_SFLIGHT[] IS NOT INITIAL. .
CL_DEMO_OUTPUT=>DISPLAY( LT_SFLIGHT[] ).
ELSE.
  WRITE '조건에 일치하는 데이터가 없습니다.'.
ENDIF.