[OpenSQL] WITH - CTE 서브쿼리

OpenSQL WITH

CTE(Common Table Expression)

 

ABAP New syntax인 WITH 문은 Open SQL에서 서브쿼리에 별칭(CTE)을 붙여서 쿼리내에서 재사용을 간편하게 할수 있도록 합니다.

 

복잡한 쿼리문 같은 경우 WITH로 서브쿼리화 하여 쿼리문을 작성하면 시인성도 좋아지며 동일 쿼리내에서 재사용하기도 수월합니다.

각각의 CTE는 쉼표(,)로 구분되며 이름 앞에 + 기호를 붙여야 합니다.

 

# 사용예시

TABLES MARC.

PARAMETERS: P_WERKS TYPE T001W-WERKS MEMORY ID WRK OBLIGATORY.
SELECT-OPTIONS: S_MATNR FOR MARC-MATNR.

START-OF-SELECTION.

WITH +T001W AS ( SELECT WERKS,
                        EKORG,
                        NAME1
                 FROM T001W ),
     +MARC AS  ( SELECT MATNR,
                        WERKS,
                        CASE WHEN SOBSL EQ '30' THEN '3'
                             ELSE '0' END AS ESOKZ
                 FROM MARC ),
     +EINE AS  ( SELECT A~MATNR,
                        A~LIFNR,
                        B~WERKS,
                        B~ESOKZ
                 FROM EINA AS A
                   INNER JOIN EINE AS B ON A~INFNR EQ B~INFNR
                 WHERE A~LOEKZ EQ @SPACE
                   AND B~LOEKZ EQ @SPACE ),
     +A017 AS  ( SELECT A~KNUMH,
                        A~LIFNR,
                        A~MATNR,
                        A~ESOKZ,
                        B~WERKS,
                        B~NAME1 AS WERKS_TXT,
                        C~NAME1,
                        A~DATAB,
                        A~DATBI
                   FROM A017    AS A
                    INNER JOIN +T001W  AS B ON A~EKORG EQ B~EKORG AND A~WERKS EQ B~WERKS
                    INNER JOIN LFA1    AS C ON A~LIFNR EQ C~LIFNR
                  WHERE A~KAPPL EQ 'M'
                    AND A~KSCHL EQ 'PB00'
                    AND A~DATAB LE @SY-DATUM
                    AND A~DATBI GE @SY-DATUM )
    SELECT A~KPEIN,
           A~KMEIN,
           A~KONWA,
           A~KBETR,
           B~MATNR,
           B~WERKS,
           B~LIFNR,
           B~WERKS_TXT,
           B~NAME1,
           B~DATAB,
           B~DATBI
     FROM KONP  AS A
     INNER JOIN +A017 AS B ON B~KNUMH EQ A~KNUMH
     INNER JOIN +EINE AS C ON C~MATNR EQ B~MATNR
                          AND C~LIFNR EQ B~LIFNR
                          AND C~WERKS EQ B~WERKS
                          AND C~ESOKZ EQ B~ESOKZ
     INNER JOIN +MARC AS D ON D~MATNR EQ B~MATNR
                          AND D~WERKS EQ B~WERKS
                          AND D~ESOKZ EQ B~ESOKZ
     WHERE A~LOEVM_KO EQ @SPACE
       AND B~WERKS EQ @P_WERKS
       AND B~MATNR IN @S_MATNR
     INTO TABLE @DATA(LT_TMP).

ZCL_SALV_TEST=>DISPLAY( LT_TMP ).