sap workbench 13

#주의 

occurs 0은  table of를 대체할 떄 사용하는 문법이다. 절대 with header line의 기능을 함축하고 있지 않다!

 

 

1> table을 header를 추가해서 사용하는 방법.  + with header line # 사용하지는 말자.

**********************************************************************
"with header line 을 사용하면 sturcture 과 같이 work area로 사용할수 있다.
"하지만 header line을 없이 코딩하는 습관을 기르자.

DATA : lt_data TYPE TABLE OF sbuspart WITH HEADER LINE.

2> data: begin of + occurs 0으로 table of를 대체하고,    with header line을 사용했다.

**********************************************************************
"with header line 을 사용하면 sturcture 과 같이 work area로 사용할수 있다.
"하지만 header line을 없이 코딩하는 습관을 기르자.

DATA : lt_data TYPE TABLE OF sbuspart WITH HEADER LINE.

DATA : BEGIN OF ls_part,
         buspart TYPE sbuspart-buspartnum,
         contact TYPE sbuspart-contact,
       END OF ls_part,

       "header를 가진 lt_table을 만드는 방법 with header line
       lt_part LIKE TABLE OF ls_part- WITH HEADER LINE.

"heADER를 가진 LT_TABLE을 만드는 방법 occurs 0을 사용하기
DATA : BEGIN OF lt_part OCCURS 0,
         buspart TYPE sbuspart-buspartnum,
         contact TYPE sbuspart-contact,
       END OF lt_part,

       lt_part_2 TYPE sbuspart OCCURS 0 WITH HEADER LINE.

 

TABLES sbook.

SELECTION-SCREEN BEGIN OF BLOCK bl1 WITH FRAME TITLE TEXT-t01.
  PARAMETERS : pa_carr TYPE sbook-carrid OBLIGATORY DEFAULT 'AA'.

  "select - options는  sign option low high 를 가지고 있다.
  SELECT-OPTIONS : so_conn  FOR sbook-connid OBLIGATORY.
  PARAMETERS     : pa_custp TYPE sbook-custtype AS LISTBOX
                   VISIBLE LENGTH 20.
  "sy- datlo => 현지 날짜"
  SELECT-OPTIONS : so_fldt  FOR sbook-fldate DEFAULT sy-datum,
                   so_bkid  FOR sbook-bookid,
                   so_cutid FOR sbook-customid NO-EXTENSION NO INTERVALS.


SELECTION-SCREEN END OF BLOCK bl1.


**********************************************************************
DATA : BEGIN OF lt_data OCCURS 0,
         carrid   TYPE sbook-carrid,
         connid   TYPE sbook-connid,
         fldate   TYPE sbook-fldate,
         bookid   TYPE sbook-bookid,
         customid TYPE sbook-customid,
         custtype TYPE sbook-custtype,
         invoice  TYPE sbook-invoice,
         class    TYPE sbook-class,
       END OF lt_data,

       lv_tabix TYPE sy-tabix.

" lt_data2 like lt_data occurs 0 with header line.
" lt_datt3 like table of lt_data with header line.

"clear header 날림"   "refresh 는 몸통을 날림"
CLEAR : lt_data, lt_data[].


SELECT carrid connid fldate bookid customid custtype invoice class
  INTO CORRESPONDING FIELDS OF TABLE lt_data
  FROM sbook
  WHERE carrid EQ pa_carr
  AND connid   IN so_conn
  AND custtype EQ  pa_custp
  AND fldate   IN so_fldt
  AND bookid   IN so_bkid
  AND customid IN so_cutid.


IF sy-subrc EQ 0.

  LOOP AT lt_data.

    lv_tabix = sy-tabix.

    CASE lt_data-invoice.
      WHEN 'X'.
        lt_data-class   = 'F'.

        MODIFY lt_data INDEX lv_tabix TRANSPORTING class.
    ENDCASE.

  ENDLOOP.
  cl_demo_output=>display( lt_data[] ).
  else.
    MESSAGE s000(zc507) WITH 'Error'.

Endif.

 

1> join 을 활용한 sql 문

TABLES : sflight, sbook.

SELECTION-SCREEN BEGIN OF BLOCK bl1 WITH FRAME TITLE TEXT-x01.
  PARAMETERS     : pa_carid TYPE sflight-carrid OBLIGATORY.
  SELECT-OPTIONS : so_conid FOR sflight-connid OBLIGATORY.
  PARAMETERS     : pa_ptype TYPE sflight-planetype AS LISTBOX VISIBLE LENGTH 10.
  SELECT-OPTIONS : so_bokid FOR sbook-bookid,
                   so_cutyp FOR sbook-custtype NO INTERVALS NO-EXTENSION.

SELECTION-SCREEN END OF BLOCK bl1.


DATA : BEGIN OF ls_data,
         carrid     TYPE   sflight-carrid,
         connid     TYPE   sflight-connid,
         fldate     TYPE   sflight-fldate,
         planetype  TYPE   sflight-planetype,
         paymentsum TYPE   sflight-paymentsum,
         currency   TYPE   sflight-currency,
         bookid     TYPE sbook-bookid,
         customid   TYPE sbook-customid,
         custtype   TYPE sbook-custtype,
         class      TYPE sbook-class,
         order_date TYPE sbook-order_date,
         agencynum  TYPE sbook-agencynum,

       END OF ls_data,

       lt_data  LIKE TABLE OF ls_data,
       lv_tabix TYPE sy-tabix.

"flight 부모table sbook 자식 table  carrid connid fldate 포함

SELECT a~carrid a~connid a~fldate a~planetype a~paymentsum a~currency
       b~bookid b~customid b~custtype b~order_date b~agencynum
  INTO CORRESPONDING FIELDS OF TABLE lt_data
  FROM sflight AS a
  INNER JOIN sbook AS b
          ON a~carrid = b~carrid
         AND a~connid = b~connid
         AND a~fldate = b~fldate
  WHERE a~carrid    = pa_carid
  AND   a~connid    IN so_conid
  AND   a~planetype = pa_ptype
  AND   b~bookid    IN so_bokid
  AND   b~custtype  IN so_cutyp.

IF sy-subrc EQ 0.

  LOOP AT lt_data INTO ls_data.

    lv_tabix = sy-tabix.

    IF ls_data-custtype = 'B'.
         ls_data-class  = 'C'.
      MODIFY lt_data FROM ls_data INDEX lv_tabix
      TRANSPORTING class.

    ENDIF.

  ENDLOOP.

  cl_demo_output=>display( lt_data ).

ENDIF.

2> 3개의 table join 방법

 

*********************************************************************
TABLES : mara, marc.

SELECTION-SCREEN BEGIN OF BLOCK bl1 WITH FRAME TITLE TEXT-t01.
  PARAMETERS     : pa_werks TYPE marc-werks OBLIGATORY DEFAULT '1010'.
  SELECT-OPTIONS : so_matnr FOR mara-matnr,
                   so_mtart FOR mara-mtart,
                   so_ekgrp FOR marc-ekgrp.

SELECTION-SCREEN END OF BLOCK bl1.

DATA : BEGIN OF ls_data,
         matnr TYPE mara-matnr,
         maktx TYPE makt-maktx,
         mtart TYPE mara-mtart,
         matkl TYPE mara-matkl,
         meins TYPE mara-meins,
         tragr TYPE mara-tragr,
         pstat TYPE marc-pstat,
         dismm TYPE marc-dismm,
         ekgrp TYPE marc-ekgrp,

       END OF ls_data,

       lt_data LIKE TABLE OF ls_data.
"text join 은 outer join을 걸자"
SELECT a~matnr a~mtart a~matkl a~meins a~tragr
       b~pstat b~dismm b~ekgrp
       c~maktx
INTO CORRESPONDING FIELDS OF TABLE lt_data
  FROM mara AS a
  inner JOIN marc AS b
      ON  a~matnr = b~matnr
  inner JOIN makt AS c
   ON  a~matnr = c~matnr

  where b~werks = pa_werks
  and a~matnr in so_matnr
  and a~mtart in so_mtart
  and b~ekgrp in so_ekgrp
  and c~spras = sy-langu.

IF sy-subrc EQ 0.

  cl_demo_output=>display( lt_data ).

ENDIF.

 

 

TABLES sbuspart.

SELECTION-SCREEN BEGIN OF BLOCK bl1 WITH FRAME TITLE TEXT-t01.
  SELECT-OPTIONS : so_busnm for sbuspart-buspartnum OBLIGATORY.
  PARAMETERS : pa_ta RADIOBUTTON GROUP rg1 DEFAULT 'X',
               pa_fc RADIOBUTTON GROUP rg1.

SELECTION-SCREEN END OF BLOCK bl1.

data : lt_data TYPE TABLE of sbuspart,
       ls_data TYPE sbuspart.

clear : ls_data.
REFRESH lt_data.


SELECT BUSPARTNUM CONTACT CONTPHONO BUSPATYP
  INTO CORRESPONDING FIELDS OF TABLE lt_data
  from sbuspart
  where buspartnum in so_busnm.


CASE 'X'.
  WHEN pa_ta.
  DELETE lt_data where buspatyp <> 'TA'.

  WHEN pa_fc.
  DELETE lt_data where buspatyp <> 'FC'.
ENDCASE.





IF sy-subrc eq 0.

  cl_demo_output=>display( lt_data ).

ENDIF.

 

 

 

CASE 'X'.
   WHEN pa_ta.
    
    LOOP AT lt_data INTO ls_data where buspatyp ne 'TA'.
      DELETE lt_data INDEX sy-tabix.

    ENDLOOP.
    
   WHEN pa_fc.
    LOOP AT lt_data into ls_data where buspatyp ne 'FC'.
      DELETE lt_data INDEX

    ENDLOOP. 
   
   WHEN OTHERS.
ENDCASE.


LOOP AT lt_data INTO ls_data.
  
  CASE 'X'.
   WHEN pa_ta.
      
      if ls_data-buspatyp ne 'TA'.
        DELETE lt_data INDEX sy-tabix.
        
   WHEN pa_fc.
      IF ls_data-buspatyp ne 'FC'.
        DELETE lt_Data INDEX sy-tabix.
      ENDIF.
      
  ENDCASE.

ENDLOOP.

 

TABLES sbuspart.

SELECTION-SCREEN BEGIN OF BLOCK bl1 WITH FRAME TITLE TEXT-t01.
  SELECT-OPTIONS : so_busnm FOR sbuspart-buspartnum.
  PARAMETERS : pa_ta RADIOBUTTON GROUP gr1 DEFAULT 'X',
               pa_fc RADIOBUTTON GROUP gr1.
SELECTION-SCREEN END OF BLOCK bl1.

data : ls_data TYPE sbuspart,
       lt_data TYPE TABLE of sbuspart.

SELECT buspartnum contact contphono buspatyp
  INTO CORRESPONDING FIELDS OF TABLE lt_data
  FROM sbuspart
  WHERE buspartnum IN so_busnm.


LOOP AT lt_data INTO ls_data.

  IF ls_data-buspatyp = 'TA'.

     IF ls_data-buspatyp <> 'TA'.
        clear : ls_data-buspatyp.

       MODIFY lt_data from ls_data INDEX sy-tabix
       TRANSPORTING buspartnum contact contphono buspatyp.
     ENDIF.

     IF ls_data-buspatyp <> 'FC'.
        clear : ls_data-buspatyp.

       MODIFY lt_data from ls_data INDEX sy-tabix
       TRANSPORTING buspartnum contact contphono buspatyp.

     ENDIF.

  ENDIF.


ENDLOOP.

BREAK-POINT.

 

********************************************************
TABLES : sflight.

SELECTION-SCREEN BEGIN OF BLOCK bl1 WITH FRAME TITLE TEXT-t01.
  PARAMETERS     : pa_carid TYPE sflight-carrid OBLIGATORY DEFAULT 'LH'.
  SELECT-OPTIONS : so_conid FOR sflight-connid OBLIGATORY,
                   so_fdate FOR sflight-fldate.
SELECTION-SCREEN END OF BLOCK bl1.

DATA : BEGIN OF ls_data,
         carrid     TYPE sflight-carrid,
         carrname   TYPE zc5v07003-carrname,
         connid     TYPE sflight-connid,
         fldate     TYPE sflight-fldate,
         cityfrom   TYPE zc5v07003-cityfrom,
         cityto     TYPE zc5v07003-cityto,
         planetype  TYPE sflight-planetype,
         seatsmax_b TYPE sflight-seatsmax_b,
         url        TYPE zc5v07003-url,
       END OF ls_data.


DATA : lt_data LIKE TABLE OF ls_data.


SELECT s~carrid
       v~carrname
       s~connid
       s~fldate
       v~cityfrom
       v~cityto
       s~planetype
       s~seatsmax_b
       v~url
INTO CORRESPONDING FIELDS OF TABLE lt_data
FROM sflight AS s INNER JOIN zc5v07003 AS v
ON s~carrid = v~carrid
AND s~connid = v~connid
WHERE s~carrid = pa_carid
  AND s~connid IN so_conid
  AND s~fldate IN so_fdate.

LOOP AT lt_data INTO ls_data.

  IF ls_data-seatsmax_b EQ '30'.
    ls_data-planetype = '747-400'.

    MODIFY lt_data FROM ls_data INDEX sy-tabix
    TRANSPORTING planetype.

  ELSEIF ls_data-seatsmax_b EQ '31'.
    ls_data-planetype = 'A340-600'.

    MODIFY lt_data FROM ls_data INDEX sy-tabix
    TRANSPORTING planetype.

  ELSE.
    CLEAR :ls_data-planetype.

    MODIFY lt_data FROM ls_data INDEX sy-tabix
    TRANSPORTING planetype.

  ENDIF.

ENDLOOP.



**********************another solution 2********************************

clear ls_data.
REFRESH lt_data.

LOOP AT lt_data into ls_data.

  CASE ls_data-seatsmax_b.

  WHEN '30'.
      ls_data-planetype = '747-400'.

      MODIFY lt_data FROM ls_data INDEX sy-tabix
      TRANSPORTING planetype.


  WHEN '31'.
      ls_data-planetype = 'A340-600'.

      MODIFY lt_data FROM ls_data INDEX sy-tabix
      TRANSPORTING planetype.


  WHEN OTHERS.

    CLEAR :ls_data-planetype.

ENDCASE.


ENDLOOP.




IF  sy-subrc EQ 0.

  cl_demo_output=>display( lt_data ).

ENDIF.