CTE Open sql

SAP Program/ABAP 2017. 10. 12. 14:26


When I mentioned CTEs one year ago, the reaction was that I’m “teasing“. But well, here we are. The Open SQL boys are really a hard-working bunch.

The wish was to have a SELECT … FROM subquery in Open SQL. That’s one thing, you can use CTEs for:

    WITH
      +conns AS (
        SELECT carrname, connid, cityfrom, cityto
              FROM spfli
                JOIN scarr ON spfli~carrid = scarr~carrid
              WHERE spfli~carrid = @carrid ),
      +cnts AS (
        SELECT COUNT(*) AS cnt
               FROM +conns )
      SELECT *
             FROM +cnts
               CROSS JOIN +conns
             ORDER BY carrname, connid
             INTO CORRESPONDING FIELDS of TABLE @itab.

The new SQL keyword WITH introduces the definition of one or more CTEs. Each CTE is a kind of subquery that produces a tabular result that can be used as a data source in all subsequent queries of the same WITH statement. In the example, a general table expression +conns (where the + is a mandatory prefix of the name) creates a table of flight connections. A further expression, +cnts, determines the number of entries in the result set of +conns. The closing mainquery combines the result sets of both general table expressions using a cross join (also new in ABAP 7.51).

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

In this example, there is one CTE +cities defining a subquery including a UNION. The result set of the CTE is evaluated as the data source of the subquery of the WHERE condition of the main query of the WITH statement.

The general CTE syntax is as follows:

WITH 
  +cte1[( name1, name2, ... )] AS ( SELECT subquery_clauses ... ), 
  +cte2[( name1, name2, ... )] AS ( SELECT subquery_clauses ... ), 
   ... 
  SELECT mainquery_clauses 
         ...
         INTO ...
       ... 
[ENDWITH]. 

Each WITH statement must be completed by a mainquery that uses at least one of its CTEs as a data source and each CTE must be used in at least one other subsequent query. Up to now a CTE cannot use itself as a data source. WITH can be used as a standalone statement or after the statement OPEN CURSOR. 

The ENDWITH is an ABAP specialty that fulfills the same task as ENDSELECT. If you don’t write in to an internal table behind INTO of the main query, you open a WITH loop that must be closed with ENDWITH.

With  ( name1, name2, … ) you can redefine the column names of the tabular result sets of a CTE, e.g.

WITH 
  +connections AS ( 
    SELECT spfli~carrid, carrname, connid, cityfrom, cityto 
           FROM spfli 
           INNER JOIN scarr 
             ON scarr~carrid = spfli~carrid 
           WHERE spfli~carrid BETWEEN @from_id AND @to_id ), 
  +sum_seats AS ( 
    SELECT carrid, connid, SUM( seatsocc ) AS sum_seats 
           FROM sflight 
           WHERE carrid BETWEEN @from_id AND @to_id 
           GROUP BY carrid, connid ), 
  +result( name, connection, departure, arrival, occupied ) AS ( 
    SELECT carrname, c~connid, cityfrom, cityto, sum_seats 
           FROM +connections AS c 
             INNER JOIN +sum_seats AS s 
               ON c~carrid = s~carrid AND 
                  c~connid = s~connid ) 
  SELECT * 
         FROM +result 
         ORDER BY name, connection 
         INTO TABLE @DATA(result).

The result sets of both general table expressions +connections and +sum_seats are merged in the subquery of the general table expression +result in a join expression. An explicit name list assigns names to the resulting columns. These names are used in the mainquery to sort the results.

With the introduction of WITH, the standalone statement SELECT suddenly lost a bit of its peculiarity. In fact it can be seen as a special case of a mainquery without WITH. Therefore the terminology of queries was generalized in Open SQL: Each language element SELECT implements a query and we distinguish between mainqueries and subqueries.

 

  • A mainquery is either a standalone SELECT statement, the final query of a WITH statement, or the final query of an OPEN CURSOR statement.
  • A subquery is possible in a WHERE condition, after WITH for the definition of a CTE, or as a data source of an INSERT statement.

All queries can be combined with UNION and the most clauses are common for all queries except the ABAP specific INTO that can be used for the mainqueries of a standalone WITH and a standalone SELECT and behind a FETCH only.

 

For more information see WITH.


'SAP Program > ABAP' 카테고리의 다른 글

MCHBH LFGJA LFMON Max 값 select  (0) 2017.10.26
ABAP 7.40 Quick Reference  (0) 2017.10.24
so_object_send를 이용한 mail 전송  (0) 2017.10.11
QM QS24 Call transaction 이용한 조회가 안될경우  (0) 2017.09.13
Smartforms Barcode  (0) 2017.09.01

설정

트랙백

댓글


so_object_send function을 이용하면 sender에 internet email을 지정하여 전송할 수 있다.




FUNCTION zcm_send_mail.
*"----------------------------------------------------------------------
*"*"Local interface:
*"  IMPORTING
*"     VALUE(IS_SENDER) TYPE  ZQMS_038
*"     VALUE(IV_TITLE) TYPE  ZWF_TITLECO
*"     VALUE(IV_RETURN) TYPE  CHAR1 OPTIONAL
*"  EXPORTING
*"     VALUE(EV_RESULT) TYPE  CHAR1
*"     VALUE(EV_ERR_MSG) TYPE  STRING
*"  TABLES
*"      T_RECEIVER TYPE  ZQMY_0001
*"      T_BODY STRUCTURE  W3HTML
*"  EXCEPTIONS
*"      INVALID_USER_ERROR
*"      NO_EMAILID
*"      INVALID_EMPLOYEE_NUMBER
*"      INVALID_MYSINGLEID
*"      INVALID_ID_TYPE
*"      INVALID_WF_OBJECT
*"----------------------------------------------------------------------

  DATA lv_object_hd     TYPE sood1.
  DATA lv_object_type          TYPE sood-objtp.
  DATA lv_originator  TYPE soos1-recextnam,
         lv_origin_type TYPE soos1-recesc.

  DATA lt_objcont       TYPE STANDARD TABLE OF soli WITH HEADER LINE.
  DATA lt_receivers     TYPE STANDARD TABLE OF soos1 WITH HEADER LINE.
  DATA lt_packing_list  TYPE STANDARD TABLE OF soxpl WITH HEADER LINE.
*
  DATAlv_lines     TYPE i,
        lo_ex_system TYPE REF TO cx_ai_system_fault,
        lo_ex_app    TYPE REF TO cx_ai_application_fault.

  CLEAR ev_resultev_err_msg", ls_en_request.

*--------------------------------------------------------------------*
* 0. Check Input Value
*--------------------------------------------------------------------*
  IF t_receiver[] IS INITIAL ).
    ev_err_msg 'Enter mail receiver (required field)'.
    ev_result  'E'.
    EXIT.
  ELSEIF t_body[] IS INITIAL ).
    ev_err_msg 'Enter mail body (required field)'.
    ev_result  'E'.
    EXIT.
  ENDIF.

*  PERFORM check_mailuser USING    is_sender  iv_return
*                         CHANGING ev_err_msg.
*  IF ev_err_msg IS NOT INITIAL.
*    ev_result = c_e.  EXIT.
*  ENDIF.

*--------------------------------------------------------------------*
* 3. Set Mail Body
*--------------------------------------------------------------------*
  CLEAR lv_object_hd.
  lv_object_hd-objla  sy-langu.
  lv_object_hd-objdes iv_title.
  lv_object_hd-objnam 'MESSAGE'.
  lv_object_hd-objsns 'O'.
  lv_object_hd-file_ext 'HTM'.
  lv_object_hd-objpri '1'.
  lv_object_hd-objcp  'X'.

  lv_object_type 'RAW'.

  LOOP AT t_body.

    lt_objcont-line t_body.
    APPEND lt_objcontCLEAR lt_objcont.

  ENDLOOP.

*  READ TABLE t_body INDEX 1.
*  SEARCH t_body FOR '<HTML>' IN CHARACTER MODE STARTING AT 1 ENDING AT 10.
*  IF sy-subrc EQ 0.
*    ls_en_request-header_helper_csvo-b_html_content_check = 'true'.
*  ENDIF.


*--------------------------------------------------------------------*
* 4. Set Recipient Info
*--------------------------------------------------------------------*
*   i_seq_id (필수값) - 일련번호 (0부터 순차적으로 증가)
*   rec_type (필수값) - 수신형태 (t : 수신, c : 참조, b : 비밀참조)
*   rec_addr (필수값) - 수신자메일주소

  CLEARlt_receiverslt_receivers[].
  LOOP AT t_receiver.

*    PERFORM check_mailuser USING    t_receiver  iv_return
*                           CHANGING ev_err_msg.
*    IF ev_err_msg IS NOT INITIAL  AND iv_return IS INITIAL.
*      ev_result = 'E'.
*      EXIT.
*    ENDIF.

    TRANSLATE  t_receiver-emailid TO LOWER CASE.
    lt_receivers-recextnam  t_receiver-emailid.
    lt_receivers-sndart 'INT'.
    lt_receivers-sndpri '1'.
    lt_receivers-recesc 'U'.

    APPEND lt_receiversCLEAR lt_receivers.
  ENDLOOP.

  lv_originator is_sender-emailid.
  lv_origin_type 'U'.

  CHECK ev_result NE 'E'.

*--------------------------------------------------------------------*
* 5. Set Resource Info
*--------------------------------------------------------------------*
*   email    (필수값) - 발신자메일주소
*   locale   (필수값) - 로케일정보 (ko_KR : 한국, 중국 : zh_CN, 일본 : ja_JP, 나머지 : en_US)
*   encoding (필수값) - 메일인코딩정보 (euc-kr 또는 utf-8)

*--------------------------------------------------------------------*
* 6. Send Mail
*--------------------------------------------------------------------*
*************************&&메일을 전송하는 FUNCTION.....

  TRY.

      CALL FUNCTION 'SO_OBJECT_SEND'
        EXPORTING
          object_hd_change           lv_object_hd
          object_type                lv_object_type
          owner                      sy-uname
          originator                 lv_originator
          originator_type            lv_origin_type
        TABLES
          objcont                    lt_objcont
          receivers                  lt_receivers
        EXCEPTIONS
          active_user_not_exist      1
          communication_failure      2
          component_not_available    3
          folder_not_exist           4
          folder_no_authorization    5
          forwarder_not_exist        6
          note_not_exist             7
          object_not_exist           8
          object_not_sent            9
          object_no_authorization    10
          object_type_not_exist      11
          operation_no_authorization 12
          owner_not_exist            13
          parameter_error            14
          substitute_not_active      15
          substitute_not_defined     16
          system_failure             17
          too_much_receivers         18
          user_not_exist             19
          originator_not_exist       20
          x_error                    21.
    CATCH cx_ai_system_fault INTO lo_ex_system.
      ev_result  'E'.
      ev_err_msg lo_ex_system->if_message~get_text).
    CATCH cx_ai_application_fault INTO lo_ex_app.
      ev_result  'E'.
      ev_err_msg lo_ex_app->if_message~get_text).
  ENDTRY.

  IF sy-subrc 0.
    "SO_OBJECT_SEND 사용시에는 commit work 해줘야 함.
    COMMIT WORK.

    ev_result 'S'.
    EXIT.
  ELSE.
    ev_result sy-subrc.
    EXIT.
  ENDIF.

ENDFUNCTION.

'SAP Program > ABAP' 카테고리의 다른 글

ABAP 7.40 Quick Reference  (0) 2017.10.24
CTE Open sql  (0) 2017.10.12
QM QS24 Call transaction 이용한 조회가 안될경우  (0) 2017.09.13
Smartforms Barcode  (0) 2017.09.01
SAP Memory ID 확인  (0) 2017.09.01

설정

트랙백

댓글


MATDOC의 BSTAUS_SG에 따라서 재고유형이 달라짐
           정의되지 않음
A 즉시 출고 가능 => 가용재고
B 품질 검사 
C 보류 재고 반품
D 보류 재고
E 제한 사용
F 재고 이전
G 고객 제공 자재
H 운송
K 고객 위탁(즉시 출고 가능)
L 품질 검사에서 고객 위탁
M 고객 위탁(사용 제한)
N 고객 반환용 포장재(즉시 출고 가능)
O 품질 검사에서 고객 반환용 포장재 
P 고객 반환용 포장재(사용 제한)
Q 공급업체에 제공된 즉시 출고 가능 자재
R 품질 검사에서 공급업체에 제공된 자재
S 공급업체에 제공된 자재 재고(사용 제한)
T 사용 용기
V 소비
W 평가 입고 보류 재고
U 외주를 위한 이전 중 재고(플랜트 간)
X 고객 위탁을 위한 이전 중 재고(플랜트 간)


'SAP Business > MM' 카테고리의 다른 글

MM Movement type 정리  (0) 2018.03.26
MM T-code 실습  (0) 2017.08.18
자재문서 취소시에 전기월이 다르면 취소하지 못하게  (0) 2017.04.11
SAP Reservation and Goods Issue  (0) 2017.03.17
SAP GR 개념 자료  (0) 2017.03.17

설정

트랙백

댓글