*&---------------------------------------------------------------------*
*& Report  ZZ_##_SQL                                                   *
*&                                                                     *
*&---------------------------------------------------------------------*
*&                                                                     *
*&                                                                     *
*&---------------------------------------------------------------------*

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 '20061101' AND '20061130'.
*  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.