Subqueries (SAP Library - ABAP Programming (BC-ABA))
Page 1 of 3
Subqueries
AsubqueryisaspecialSELECTstatementcontainingasubquerywithinparticularconditionsoftheWHERE
orHAVINGclauses.YoucannotusethemintheONconditionoftheFROMclause.Theirsyntaxis:
(SELECT<result>
FROM<source>
[WHERE<condition>]
[GROUPBY<fields>]
[HAVING<cond>])
Asyoucansee,thissyntaxisrestrictedincomparisonwiththefullSELECTstatement,sinceitcontainsno
INTOorORDERBYclause.
Youcannestsubqueries,thatis,theWHEREandHAVINGclausesofsubqueriescanthemselvescontaina
subquery.WhenanestedsubqueryintheWHEREclauseusesfieldsfromthepreviousquery,itisknownas
acorrelatedquery.Thesubqueryisthenprocessedforeachlineofthedatabasetablethatsatisfiesthe
previouscondition.
Scalar Subqueries
Inascalarsubquery,theselectionintheSELECTclauseisrestrictedtoonecolumnoraggregateexpression.
Theexpression<result>oftheSELECTclauseis:
...<line>[<agg>]<s>
YoucanonlyenterasinglefieldintheSELECTclause.
Subqueries in Conditions
Anon-scalarsubquerycanonlyhaveaWHEREorHAVINGclause
inthe[NOT]EXISTS<subquery>condition.Thisconditionistrueiftheresultsetofthesubquery
containsatleastone[no]line.
Scalar Subqueries in Conditions
Aswellasintheabovecondition,youcanalsousescalarsubqueriesinfurtherconditions.
Checking a Value of the Subquery
Thefollowingisapossibleconditionwithscalarsubqueries:
...<s>NOTIN<subquery>...
Theconditionistrueifthevalueof<s>is[not]containedintheresultssetofthescalarsubquery<subquery>.
Scalar Subqueries in Comparisons
Theotherconditionscanallbecomparisonswhoseoperatorsarecontainedinthetableforcomparisonswith
alltypesintheWHEREclause.Thereisadifference,dependingonwhetherthesubqueryselectioncontains
oneormorelines.
Single-line Subquery
Iftheselectioninthesubqueryonlycontainsoneline,usethefollowingforthecomparison:
...<s><operator><subquery>...
Thevalueof<s>iscomparedwiththevalueintheselectionfromthesubquery.Theconditioniseithertrueof
false.
Thesubquerymayonlycontainoneline,otherwisearuntimeerroroccurs.Aone-linesubquerycanbe
createdbycompletelydefiningthekeyintheWHEREclause.
Multiple-line Subquery
Iftheselectionfromthesubqueryreturnsmorethanoneline,youmustwritethecomparisonasfollows:
...<s><operator>ALL|ANY|SOME<subquery>...
IfyouusetheALLprefix,theconditionisonlytrueifthecomparisonistrueforalllinesinthesubquery.Ifyou
usetheANYorSOMEprefix,theconditionisonlytrueifthecomparisonistrueforatleastonelineofthe
subquery.Theequalityoperator(=orEQ)inconjunctionwithANYorSOMEhasthesameeffectastheIN
operatorforcheckingavalue.
IftheselectionfromthesubquerycontainsseverallinesandyoudonotusetheALL,ANY,orSOME
expression,aruntimeerroroccurs.
http://help.sap.com/saphelp_nw04/helpdata/en/dc/dc7614099b11d295320000e8353423/... 9/1/2010
Subqueries (SAP Library - ABAP Programming (BC-ABA))
Page 2 of 3
Correlated,non-scalarsubquery:
REPORTdemo_select_subquery_1.
DATA:name_tabTYPETABLEOFscarr-carrname,
nameLIKELINEOFname_tab.
SELECTcarrname
INTOTABLEname_tab
FROMscarr
WHEREEXISTS(select*
FROMspfli
WHEREcarrid=scarr~carridAND
cityfrom='NEWYORK').
LOOPATname_tabINTOname.
WRITE:/name.
ENDLOOP.
ThisexampleselectsalllinesfromdatabasetableSCARRforairlinesthatflyfromNewYork.
Scalarsubquery:
REPORTdemo_select_subquery_2.
DATA:carr_idTYPEspfli-carridVALUE'LH',
conn_idTYPEspfli-connidVALUE'0400'.
DATA:cityTYPEsgeocity-city,
latiTYPEpDECIMALS2,
longiTYPEpDECIMALS2.
SELECTSINGLEcitylatitudelongitude
INTO(city,lati,longi)
FROMsgeocity
WHEREcityIN(selectcityfrom
FROMspfli
WHEREcarrid=carr_idAND
connid=conn_id).
WRITE:city,lati,longi.
ThisexamplereadsthelatitudeandlongitudeofthedeparturecityofflightLH402fromdatabase
tableSGEOCITY.
Scalarsubquery:
REPORTdemo_select_subquery_3.
DATA:waTYPEsflight,
planeLIKEwa-planetype,
seatsLIKEwa-seatsmax.
SELECTcarridconnidplanetypeseatsmaxMAX(seatsocc)
INTO(wa-carrid,wa-connid,wa-planetype,
wa-seatsmax,wa-seatsocc)
FROMsflight
GROUPBYcarridconnidplanetypeseatsmax
ORDERBYcarridconnid.
WRITE:/wa-carrid,
wa-connid,
wa-planetype,
wa-seatsmax,
wa-seatsocc.
HIDE:wa-carrid,wa-connid,wa-seatsmax.
ENDSELECT.
ATLINE-SELECTION.
WINDOWSTARTINGAT453ENDINGAT8513.
WRITE:'AlternativePlaneTypes',
'for',wa-carrid,wa-connid.
http://help.sap.com/saphelp_nw04/helpdata/en/dc/dc7614099b11d295320000e8353423/... 9/1/2010
Subqueries (SAP Library - ABAP Programming (BC-ABA))
Page 3 of 3
ULINE.
SELECTplanetypeseatsmax
INTO(plane,seats)
FROMsaplaneASplane
WHEREseatsmax<wa-seatsmaxAND
seatsmax>=ALL(selectseatsocc
FROMsflight
WHEREcarrid=wa-carridAND
connid=wa-connid)
ORDERBYseatsmax.
WRITE:/plane,seats.
ENDSELECT.
Thelistoutput,afterdouble-clickingaline,lookslikethis:
Thedetaillistdisplaysallaircrafttypesthathavefewerseatsthanthecurrently-allocatedaircraft
type,butenoughtocarryallofthepassengerscurrentlybookedontheflight.
http://help.sap.com/saphelp_nw04/helpdata/en/dc/dc7614099b11d295320000e8353423/... 9/1/2010
