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 C4AWHERE SPRSL = 'D' AND
ARBGB = '00'.
No comments:
Post a Comment