OPEN SQL 和 native sql技巧

Specifying columns Dynamically

 

DATA: itab TYPE STANDARD TABLE OF spfli,
      wa LIKE LINE OF itab.

DATA: line(72) TYPE c,
      list LIKE TABLE OF line(72).

line = ' CITYFROM CITYTO '.
APPEND line TO list.

SELECT DISTINCT (list)
  INTO CORRESPONDING FIELDS OF TABLE itab
  FROM spfli.

 

 

INTO CORRESPONDING FIELDS OF

以前经常用 into corresponding fields of table itab,其实into corresponding fields of stru也是经常用的

DATA: BEGIN OF luggage,
        average TYPE p DECIMALS 2,
        sum     TYPE p DECIMALS 2,
      END OF luggage.

SELECT AVG( luggweight ) AS average SUM( luggweight ) AS sum
  INTO CORRESPONDING FIELDS OF luggage
  FROM sbook.

WRITE: / 'Average:', luggage-average,
       / 'Sum    :', luggage-sum.

 

 

specifying the sounrce dynamically

DATA wa TYPE scarr.

DATA name(10) TYPE c VALUE 'SCARR'.

SELECT  *
  INTO  wa
  FROM  (name) CLIENT SPECIFIED
  WHERE mandt = '000'.

  WRITE: / wa-carrid, wa-carrname.

ENDSELECT.

 

dynamic conditions

DATA: cond(72) TYPE c,
      itab LIKE TABLE OF cond.

PARAMETERS: city1(10) TYPE c, city2(10) TYPE c.

DATA wa TYPE spfli-cityfrom.

CONCATENATE 'CITYFROM = ''' city1 '''' INTO cond.
APPEND cond TO itab.
CONCATENATE 'OR CITYFROM = ''' city2 '''' INTO cond.
APPEND cond TO itab.
CONCATENATE 'OR CITYFROM = ''' 'BERLIN' '''' INTO cond.
APPEND cond TO itab.

LOOP AT itab INTO cond.
  WRITE cond.
ENDLOOP.

SKIP.

SELECT  cityfrom
  INTO  wa
  FROM  spfli
  WHERE (itab).

  WRITE / wa.

ENDSELECT.

 

for all entries function

DATA: cond(72) TYPE c,
      itab LIKE TABLE OF cond.

PARAMETERS: city1(10) TYPE c, city2(10) TYPE c.

DATA wa TYPE spfli-cityfrom.

CONCATENATE 'CITYFROM = ''' city1 '''' INTO cond.
APPEND cond TO itab.
CONCATENATE 'OR CITYFROM = ''' city2 '''' INTO cond.
APPEND cond TO itab.
CONCATENATE 'OR CITYFROM = ''' 'BERLIN' '''' INTO cond.
APPEND cond TO itab.

LOOP AT itab INTO cond.
  WRITE cond.
ENDLOOP.

SKIP.

SELECT  cityfrom
  INTO  wa
  FROM  spfli
  WHERE (itab).

  WRITE / wa.

ENDSELECT.

 

exists/in

DATA: name_tab TYPE TABLE OF scarr-carrname,
      name  LIKE LINE OF name_tab.

SELECT  carrname
  INTO  TABLE name_tab
  FROM  scarr
  WHERE EXISTS ( select  *
                   FROM  spfli
                   WHERE carrid   =  scarr~carrid AND
                         cityfrom = 'NEW YORK'        ).

LOOP AT name_tab INTO name.
  WRITE: / name.
ENDLOOP.

总结：IN适合于外表大而内表小的情况；EXISTS适合于外表小而内表大的情况。IN走的Merge路线，EXISTS走的是IN路线。

 

native sql

DATA: c1 TYPE cursor,
      c2 TYPE cursor.

DATA: wa1 TYPE spfli,
      wa2 TYPE spfli.

DATA: flag1(1) TYPE c,
      flag2(1) TYPE c.

OPEN CURSOR: c1 FOR SELECT  carrid connid
                      FROM  spfli
                      WHERE carrid = 'LH',

             c2 FOR SELECT  carrid connid cityfrom cityto
                      FROM  spfli
                      WHERE carrid = 'AZ'.

DO.
  IF flag1 NE 'X'.
    FETCH NEXT CURSOR c1 INTO CORRESPONDING FIELDS OF wa1.
    IF sy-subrc <> 0.
      CLOSE CURSOR c1.
      flag1 = 'X'.
    ELSE.
      WRITE: / wa1-carrid, wa1-connid.
    ENDIF.
  ENDIF.
  IF flag2 NE 'X'.
    FETCH NEXT CURSOR c2 INTO CORRESPONDING FIELDS OF wa2.
    IF sy-subrc <> 0.
      CLOSE CURSOR c2.
      flag2 = 'X'.
    ELSE.
      WRITE: / wa2-carrid, wa2-connid,
               wa2-cityfrom, wa2-cityto.
    ENDIF.
  ENDIF.
  IF flag1 = 'X' AND flag2 = 'X'.
    EXIT.
  ENDIF.
ENDDO.