REPORT ZZ_##_SQL.
DATA waspfli LIKE spfli.
DATA wasflight LIKE sflight.
DATA wasbook LIKE sbook.
DATA wascarr LIKE scarr.
DATA wascustom LIKE scustom.
***********
* BETWEEN *
***********
*SELECT *
* FROM sbook
* INTO wasbook
* WHERE carrid = 'LH' AND
* connid = '0400' AND
* fldate BETWEEN '20070301' AND '20070331'.
* WRITE: / wasbook-fldate, wasbook-bookid, wasbook-passname.
*ENDSELECT.
********
* LIKE *
********
*SELECT *
* FROM sbook
* INTO wasbook
* WHERE passname LIKE '%art%'
* ORDER BY carrid connid fldate.
* WRITE: / wasbook-carrid,
* wasbook-connid,
* wasbook-fldate,
* wasbook-bookid,
* wasbook-passname.
*ENDSELECT.
******
* IN *
******.
*SELECT *
* FROM scustom
* INTO wascustom
* WHERE country IN ('AT', 'CH', 'DE')
* ORDER BY city name.
* WRITE: / wascustom-city, wascustom-name.
*ENDSELECT.
*SELECT *
* FROM scustom
* INTO wascustom
* WHERE country IN ('AT', 'CH', 'DE')
* ORDER BY city descending name ascending.
* WRITE: / wascustom-city, wascustom-name.
*ENDSELECT.
********************************
* MAX, MIN, AVG, SUM, GROUP BY *
********************************
*DATA count TYPE I.
*DATA min TYPE P DECIMALS 2.
*DATA max TYPE P DECIMALS 2.
*DATA avg TYPE P DECIMALS 2.
*DATA sum TYPE P DECIMALS 2.
*DATA connid LIKE sbook-connid.
*DATA fldate LIKE sbook-fldate.
*
*SELECT connid fldate COUNT( * )
* MIN( luggweight ) MAX( luggweight )
* AVG( luggweight ) SUM( luggweight )
* INTO (connid, fldate, count, min, max, avg, sum)
* FROM sbook
* WHERE carrid = 'LH '
* GROUP BY connid fldate.
* WRITE: / connid, fldate, count, min, max, avg, sum.
*ENDSELECT.
****************************************
* MAX, MIN, AVG, SUM, GROUP BY, HAVING *
****************************************
*DATA count TYPE I.
*DATA min TYPE P DECIMALS 2.
*DATA max TYPE P DECIMALS 2.
*DATA avg TYPE P DECIMALS 2.
*DATA sum TYPE P DECIMALS 2.
*DATA connid LIKE sbook-connid.
*DATA fldate LIKE sbook-fldate.
*
*SELECT connid fldate COUNT( * )
* MIN( luggweight ) MAX( luggweight )
* AVG( luggweight ) SUM( luggweight )
* INTO (connid, fldate, count, min, max, avg, sum)
* FROM sbook
* WHERE carrid = 'LH '
* GROUP BY connid fldate
* HAVING AVG( luggweight ) >= 15.
* WRITE: / connid, fldate, count, min, max, avg, sum.
*ENDSELECT.
********************
* JOIN (INNER), AS *
********************
*PARAMETERS abflugo LIKE spfli-cityfrom.
*
*START-OF-SELECTION.
*
*DATA wacityto LIKE spfli-cityto.
*DATA wacarrname LIKE scarr-carrname.
*DATA wafltime LIKE spfli-fltime.
*
*WRITE: 'Abflüge von', abflugo.
*SKIP.
*
*SELECT a~cityto b~carrname a~fltime
* FROM spfli AS a INNER JOIN scarr AS b ON a~carrid = b~carrid
* INTO (wacityto, wacarrname, wafltime)
* WHERE a~cityfrom = abflugo.
* WRITE: / wacityto, wacarrname, wafltime.
*ENDSELECT.
*********************
* JOIN (LEFT OUTER) *
*********************
*SELECT scustom~name scustom~street
* sbook~carrid sbook~connid sbook~fldate sbook~bookid
* INTO (wascustom-name, wascustom-street, wasbook-carrid,
* wasbook-connid, wasbook-fldate, wasbook-bookid)
* FROM scustom LEFT OUTER JOIN sbook
* ON scustom~id = sbook~customid
* WHERE scustom~city = 'Hamburg'
* ORDER BY scustom~name sbook~fldate.
* WRITE: / wascustom-name, wascustom-street,
* wasbook-carrid, wasbook-connid,
* wasbook-fldate, wasbook-bookid.
*ENDSELECT.
************
* DISTINCT *
************
*SELECT passname
* FROM sbook
* INTO CORRESPONDING FIELDS OF wasbook
* ORDER by passname.
* WRITE / wasbook-passname.
*ENDSELECT.
*SELECT DISTINCT passname
* FROM sbook
* INTO CORRESPONDING FIELDS OF wasbook
* ORDER BY passname.
* WRITE / wasbook-passname.
*ENDSELECT.
****************************
* Dynamische WHERE-Klausel *
****************************
*TYPES myStrings(255) TYPE C.
*
*DATA myString TYPE myStrings.
*DATA whereClause TYPE myStrings.
*DATA whereClauseTab TYPE STANDARD TABLE OF myStrings.
*
*REFRESH whereClauseTab.
*
*myString = 'Fischer'.
*CONCATENATE 'passname LIKE ''%' myString '%'' OR' INTO whereClause.
*APPEND whereClause TO whereClauseTab.
*
*myString = 'Meier'.
*CONCATENATE 'passname LIKE ''%' myString '%'' OR' INTO whereClause.
*APPEND whereClause TO whereClauseTab.
*
*myString = 'Schneider'.
*CONCATENATE 'passname LIKE ''%' myString '%'' OR' INTO whereClause.
*APPEND whereClause TO whereClauseTab.
*
*myString = 'Sommer'.
*CONCATENATE 'passname LIKE ''%' myString '%''' INTO whereClause.
*APPEND whereClause TO whereClauseTab.
*
*SELECT *
* FROM sbook
* INTO wasbook
* WHERE (whereClauseTab)
* ORDER BY carrid connid fldate.
* WRITE: / wasbook-carrid,
* wasbook-connid,
* wasbook-fldate,
* wasbook-bookid,
* wasbook-passname.
*ENDSELECT.
************************************************************************
* Hinweis: *
* Ebenso bei SELECT, FROM, GROUP BY, HAVING und ORDER BY *
* Statt mit internen Tabellen auch mit einfachen Zeichenketten möglich *
************************************************************************
**************
* Subqueries *
**************
*DATA seatsocc LIKE sflight-seatsocc.
*
*SELECT MAX( seatsocc ) FROM sflight INTO seatsocc.
*
*WRITE seatsocc.
*
*ULINE.
*
*SELECT * FROM sflight INTO wasflight
* WHERE seatsocc = ( SELECT MAX( seatsocc ) FROM sflight ).
* WRITE: / wasflight-carrid, wasflight-connid, wasflight-fldate.
*ENDSELECT.
*
*ULINE.
*
*SELECT * FROM sflight INTO wasflight
* WHERE EXISTS ( SELECT * FROM sbook
* WHERE carrid = sflight~carrid
* AND connid = sflight~connid
* AND fldate = sflight~fldate ).
* WRITE: / wasflight-carrid, wasflight-connid, wasflight-fldate.
*ENDSELECT.
