杯子茶室

关注有趣的事物

商業智能(6) - OpenSQL

网络 0 评

OpenSQL

Table和Work Area

  • Internal Table

    • Buffer multiple lines of record

  • Work Area

    • Buffer single line of record

Define Data Types

Define data types for single-line record

TYPES:  BEGIN OF ty_wa,
        col1 TYPE c LENGTH 4,
        col2 TYPE i,
        END OF ty_wa.

Define data types for multi-line records

TYPES ty_itab TYPE TABLE OF ty_wa.

Create Work Area and Internal Table

  • Create work area with:

    • Single-line type

      • DATA wa1 TYPE ty_wa.

    • Multi-line type

      • DATA wa1 TYPE LINE OF ty_itab.

    • Table in ABAP dictionary (1)

      • DATA wa1 TYPE scarr.

    • Table in ABAP dictionary (2)

      • TABLES scarr.
      • Name of the work area is 'scarr'

    • Internal table

      • DATA wa1 LIKE LINE OF itab1.

  • Create internal table with:

    • Single-line type

      • DATA itab1 TYPE TABLE OF ty_wa.

    • Multi-line type

      • DATA itab1 TYPE ty_itab.

    • Table in ABAP dictionary

      • DATA itab1 TYPE STANDARD TABLE OF scarr.

    • Work area

      • DATA itab1 LIKE TABLE OF wa1.

Operations of Work Area

Read record from work area

WRITE: 'ID', 5 'Name'.
WRITE: / wa1-carrid UNDER 'ID',
                wa1-carrname UNDER 'Name'.
  • Hyphen(-): relationship between work area and field in ABAP

Add record into work area

CLEAR wa1.
wa1-mandt = '810'. 
wa1-carrid = 'QQ'.   
wa1-carrname = 'QQ Airlines'.

Update record into work area

wa1-currcode = 'HKD'.

Operations of Internal Table

LOOP all records

WRITE: '#', 5 'ID', 9 'Name'.
LOOP AT itab1 INTO wa1. " 有點像for wa1 in itab1
    WRITE: /(2) SY-TABIX UNDER '#',
            wa1-carrid UNDER 'ID',
            wa1-carrname UNDER 'Name'.
ENDLOOP.
  • SY-TABIX: index of table iteration

SORT the records

SORT itab1 BY carrname DESCENDING.
  • Internal table should be sorted before using control levels
SORT itab1 by item.
LOOP AT itab1 INTO wa1.
    AT FIRST.
        WRITE 'Summary'.
    ENDAT.

    AT NEW item.
        WRITE: /3 wa1-item.
    ENDAT.

    WRITE: /10(3) wa1-stock.

    AT END OF item.
        SUM.
        WRITE: /5 '  sub:', 
                    (3) wa1-stock.
    ENDAT.

    AT LAST.
        SUM.
        WRITE: / 'Total:', 
                    9(3) wa1-stock.
    ENDAT.

ENDLOOP.

READ a single (first) record with condition

READ TABLE itab1 INTO wa1 WITH KEY carrname = 'Air Berlin'.

READ a single record with INDEX

READ TABLE itab1 INTO wa1 INDEX 1.

APPEND a new record at the last position

APPEND wa1 TO itab1.

COLLECT (sum) values of numeric fields if key value is found

COLLECT wa1 INTO itab1.

INSERT a new record at a specific position

INSERT a new record at a specific position

MODIFY record with INDEX

READ TABLE itab1 INTO wa1 INDEX 1.
wa1-currcode = 'HKD'.
MODIFY itab1 FROM wa1 INDEX 1.

MODIFY records with conditions

MODIFY itab1 FROM wa1 
TRANSPORTING currcode url WHERE currcode = 'USD'.

DELETE records with conditions

DELETE itab1 WHERE currcode = 'EUR'.

DELETE records with INDEX

DELETE itab1 WHERE INDEX 1.

DELETE adjacent duplicate records

DELETE ADJACENT DUPLICATES FROM itab1 COMPARING currcode.

REFRESH – delete records

REFRESH itab1.

Select Records

Retrieve Records with SELECT statement

TYPES:  BEGIN OF ty_wa,
            mandt TYPE c LENGTH 3,
            carrid TYPE c LENGTH 3,
            carrname TYPE c LENGTH 20,
            currcode TYPE c LENGTH 5,
            url TYPE c LENGTH 255,
        END OF ty_wa.
DATA wa1 TYPE ty_wa.
DATA itab1 TYPE TABLE OF ty_wa.

SELECT * FROM scarr INTO TABLE itab1.

WRITE: 'ID', 9 'Name'.
LOOP AT itab1 INTO wa1.
    WRITE: / wa1-carrid UNDER 'ID',
            wa1-carrname UNDER 'Name'.
ENDLOOP.

or

DATA wa1 TYPE scarr.
DATA itab1 LIKE TABLE OF wa1.

SELECT * FROM scarr INTO TABLE itab1.

WRITE: 'ID', 9 'Name'.
LOOP AT itab1 INTO wa1.
    WRITE: / wa1-carrid UNDER 'ID',
            wa1-carrname UNDER 'Name'.
ENDLOOP.
可以看到，selsct一定要把數據存到table data裡面，而table可以被loop進行循環，放入type裡面。這裡可以淺顯地將table理解為單一type的數組。

Read Specific records

TYPES:  BEGIN OF ty_wa,
            . . .
        END OF ty_wa.
DATA wa1 TYPE ty_wa.
DATA itab1 TYPE TABLE OF ty_wa.

SELECT * FROM scarr INTO TABLE itab1
WHERE scarr~currcode = 'USD'.
TYPES:  BEGIN OF ty_wa,
            carrid TYPE c LENGTH 3,
            carrname TYPE c LENGTH 20,
        END OF ty_wa.
DATA wa1 TYPE ty_wa.
DATA itab1 TYPE TABLE OF ty_wa.

SELECT scarr~carrid carrname
FROM scarr INTO TABLE itab1.
使用的是where和select的列選擇，其實和普通sql的語句差不多。

讀取單一記錄

DATA wa1 TYPE scarr.

SELECT SINGLE * FROM scarr INTO wa1.        " no TABLE

WRITE: 'ID', 5 'Name'.
WRITE: / wa1-carrid UNDER 'ID',
        wa1-carrname UNDER 'Name'.

使用Select循環

DATA wa1 TYPE scarr.

SELECT * FROM scarr INTO wa1.    " OpenSQL
    WRITE wa1-carrid.             " ABAP
ENDSELECT.                     " OpenSQL

利用Work Area避免使用data

TABLES scarr.

SELECT * FROM scarr.        " no INTO clause
    WRITE scarr-carrid.
ENDSELECT.

Inner Join

TYPES:  BEGIN OF ty_wa,
             carrname TYPE c LENGTH 20,
             fldate TYPE d,
             END OF ty_wa.
DATA itab1 TYPE TABLE OF ty_wa.

SELECT carrname fldate
FROM scarr
  INNER JOIN sflight
    ON scarr~mandt = sflight~mandt
    AND scarr~carrid = sflight~carrid
INTO TABLE itab1.

Return(SY-SUBRC)

  • Return code

    • SY-SUBRC = 0 if record is found
    • SY-SUBRC <> 0 if no record is found / error occurs
SELECT SINGLE * FROM scarr INTO TABLE ws1.
IF SY-SUBRC <> 0.
    WRITE 'No record'.
ELSE.
    WRITE: / wa1-carrid … .
ENDIF.

INSERT

INSERT INTO zscarr VALUES wa1.
INSERT zscarr FROM wa1.
INSERT zscarr FROM TABLE itab1.

Delete

DELETE FROM zscarr WHERE carrname = 'American Airlines'.
DELETE zscarr FROM wa1.
DELETE zscarr FROM TABLE itab1.

UPDATE

UDPATE zscarr SET currcode = 'HKD' WHERE currcode = 'USD'.

DATA wa1 TYPE zscarr.
SELECT SINGLE * INTO wa1 FROM zscarr WHERE carrid = 'AA'.
wa1-carrname = 'AA Airlines'.

UPDATE zscarr FROM wa1.

Update records with internal table

DATA: wa1 TYPE zscarr,
         itab1 LIKE TABLE OF zscarr.
SELECT * INTO TABLE itab1 FROM zscarr WHERE currcode = 'USD'.

LOOP AT itab1 INTO wa1.
    wa1-currcode = 'HKD'.
    MODIFY itab1 FROM wa1
            INDEX SY-TABIX.
ENDLOOP.

UPDATE zscarr FROM TABLE itab1.

Modify

若存在key value,則update,否則insert

MODIFY zscarr FROM TABLE itab1.
MODIFY zscarr FROM ws1.
商業智能(7) - Selection Screen
发表评论
撰写评论
△