Open SQL syntax examples


  1. Working with single entries
  2. Reading all entries into an internal table
  3. Reading single entries
  4. Selecting single fields
  5. Append new record
  6. Update with where clause
  7. For all entries
  8. JOINs in open SQL
    1. INNER JOIN
    2. LEFT OUTER JOIN
    3. Example 1
    4. Example 2
  9. Using IN in logical expressions and sql statements
  10. Using aggergate functions

Working with single entries


select * from ska1
   where saknr = '77004500'.
* Do something with data    
   move-corresponding ska1 to itab.
  append itab.

endselect.

Reading all entries into an internal table


This is more efficient that example 1

select * from ska1 into table itab
    where    saknr like '77%'
    order by saknr.

Reading single entries


You must specify the full primarykey in the where clause to get a correct result.

tables: zsd00004.
data l_custname like like zsd00004-zcustname.
 
  
  SELECT SINGLE zcustname into l_custname FROM  zsd00004
     WHERE  zcustno  = '1551'.

Selecting single fields


This could improve effciency for database tables with many entries and many fields.

data: l_kunnr like kna1-kunnr,
         l_kukla like kna1-kukla.



SELECT kunnr kukla
  INTO (l_kunnr,l_kukla)
  FROM  kna1.
 
  write: / l_kunnr, l_kukla.
ENDSELECT.


Append new record


Tables: zsd00003.


  zsd00003-zprogram = g_screen0100-zprogram.
  zsd00003-zstep = g_screen0100-zstep.
  zsd00003-zenhed = g_screen0100-zenhed.

  INSERT INTO zsd00003 VALUES zsd00003.

  IF sy-subrc = 4.

SY-SUBRC = 0: Line inserted.
SY-SUBRC = 4: The line could not be inserted. A line with the same key
already existed.


Update with where clause



This statement updates the fields zlogdato and zlogtid for the records that
satisfies the where clause.

update zsd00003 set zlogdato = sy-datum
                                    zlogtid  = sy-uzeit
         where  zdriftscenter     = g_screen0100-zdriftscenter.

For all entries


tables: mara, makt.

data: begin of i_material occurs 0,
        matnr like mara-matnr,
        maktx like makt-maktx,
      end of i_material.



start-of-selection.

  select matnr
    appending corresponding fields of table i_material
    from mara.

  select matnr maktx
    into corresponding fields of table i_material
    from makt
    for all entries in i_material
    where matnr = i_material-matnr and
          spras = sy-langu.

end-of-selection.
  loop at i_material.
    write: / i_material-matnr, i_material-maktx.
  endloop.

JOINs in open SQL


INNER JOIN


One or more lines on the right-hand table is linked to a line in the
left-hand table. Lines from the left-handed table
is only selected if they meet the ON criteria.


                   FROM from vbak as a inner join vbap as b
                             ON b~vbeln = a~vbeln.

One or more lines from vbap is selected for each line in vbak.

Limits on the ON clause:

LEFT OUTER JOIN


The left outer join read lines from the left-handed table EVEN if there is
no corresponding line in the right hand table.

                   FROM vbak as a left outer join vbap as b
                             ON b~vbeln = a~vbeln.

If  vbap does not contain any lines that meets the condition, a single line
where the values from vbap is filled with null values.


Example 1


SELECT a~zafstemnr b~zafstemnr b~zsaknr               

    INTO CORRESPONDING FIELDS OF TABLE i_tab            
    FROM zcostafstm as a INNER JOIN zcostplan as b      
    ON b~zafstemnr = a~zafstemnr.                       



Example 2


SELECT lips~vbeln lips~lfimg likp~wadat
    INTO corresponding fields of table ltab_orders
    FROM lips JOIN likp on ( lips~vbeln = likp~vbeln )
                       JOIN vbfa on ( lips~vbeln = vbfa~vbelv )
   WHERE      lips~matnr = matnr
                      and lips~vbeln in vbeln

                      and likp~vbeln in vbeln
                      and vbfa~vbelv in vbeln
                      and lips~werks in werks
                      and likp~wadat in datum
                      and vbfa~vbtyp_n = 'Q' "WMS transfer order
                     and vbfa~vbtyp_v = 'J'. "Delivery


From ABAP hints & Tips:  http://oasis.fortunecity.com/skegness/110/JOIN.html
 

Using IN in logical expressions and sql statements

It is possible to use  a selection table if together with the IN operator.

Example:

* Selection table
 DATA: BEGIN OF it_select_fkart OCCURS 0,
        sign(1),
        option(2),
        low  LIKE zsd00010-fkart,
        high LIKE zsd00010-fkart,
     END  OF it_select_fkart.


* Build selection table
      SELECT * FROM zsd00010
         WHERE zaftaletype = '0/0'.
         CLEAR it_select_fkart.

         it_select_fkart-sign = 'I'.
         it_select_fkart-option = 'EQ'.
         it_select_fkart-low = zsd00010-fkart.
*        it_select_fkart-high = .  "Optional
         APPEND it_select_fkart.
      ENDSELECT.

* Select statement
  SELECT   vbeln
           zuonr
           kunag
           fkart
           fkdat
   INTO   CORRESPONDING FIELDS OF TABLE i_indlaes_vbrk
   FROM    vbrk
   WHERE   kunag IN  s_kunag AND
           fkart IN it_select_fkart.

  endselect.

Using aggergate functions

SELECT MAX( MSGNR ) FROM T100 INTO C4A 
 WHERE SPRSL = 'D' AND             
       ARBGB = '00'.    

Converted from CHM to HTML with chm2web Standard 2.7 (unicode)