Intro to ABAP - Chapter 11

							         ABAP Open SQL Extensions




BC170_2.11.1
                           Objectives

  • The participants will be able to:
        – Describe how to use the following in an ABAP
          Program:
               • SELECT DISTINCT Statement
               • Dynamic WHERE Clause
               • Concatenate Statement
               • Join (Inner vs. Left Outer)
               • Aliases

BC170_2.11.2
   The SELECT DISTINCT Statement
                                Expected Results




               Using SELECT
                DISTINCT, it
                 is possible
                to eliminate
               duplicate rows
                   from the
                  result set.
BC170_2.11.3
 SELECT Using Aggregate Functions
                      Expected Results




BC170_2.11.4
          The Dynamic WHERE Clause
                                    The parameters
                                  entered make up the
                                    contents of the
                                  WHERE clause. The
                                 user has the option of
                                     changing the
                                    conditions and
                                 dynamically effecting
                                     which way the
                                 program will execute.

                          An internal table is created
                            which holds the WHERE
                          clause until it is used in the
                              SELECT statement.




BC170_2.11.5
               CONCATENATE Statement
                           CONCATENATE    <source field 1>
                                          <source field 2>
                                          <source field 3>
                                          :
                                          :
                                          <source field n>
                                  INTO    <target field>
                           SEPARATED BY   <constant>.




BC170_2.11.6
         Variations on the INTO Clause
                   SELECT <FIELD> FROM <dbtable>
                           INTO TABLE <internal table>
                           PACKAGE SIZE <n>.
                   ENDSELECT.




BC170_2.11.7
           Specifying the Table Name at
                     Runtime




BC170_2.11.8
      Joins: Why We Should Use Them




  •   Joins are more efficient than logical databases and nested
      selects.
  •   They access multiple tables with one select statement.

BC170_2.11.9
                Inner Joins
SCARR                    SFLIGHT




BC170_2.11.10
                   Inner Joins Syntax

        SELECT <table1~field1 table1~field2 table2~field3. . . >
          INTO (<target >)
          FROM <table1 > INNER JOIN <table2 >
          ON <table1~keyfield1 > = <table2~keyfield1 >
            AND <table1~keyfield2 > = <table2~keyfield2 >
            AND . . .
          WHERE . . .
        ENDSELECT.




BC170_2.11.11
                  The Driving Table

        SELECT scarr~carrname sflight~carrid
          sflight~connid sflight~fldate
          INTO (carrname, carrid, connid, date)
          FROM scarr INNER JOIN sflight
          ON scarr~carrid = sflight~carrid.
               WRITE: / carrid, connid, date, carrname.
        ENDSELECT.



BC170_2.11.12
                Left Outer Joins
SCARR                       SFLIGHT




BC170_2.11.13
                Left Outer Join Syntax

        SELECT <table1~field1 table1~field2 table2~field3. . . >
          INTO (<target >)
          FROM <table1 > LEFT OUTER JOIN <table2 >
          ON <table1~keyfield1 > = <table2~keyfield1 >
            AND <table1~keyfield2 > = <table2~keyfield2 >
            AND . . .
          WHERE . . .
        ENDSELECT.




BC170_2.11.14
         Open SQL Syntax Restrictions




    The syntax for joins have been given certain restrictions in order
          to insure that they produce the same results for all
                       SAP supported databases.

BC170_2.11.15
                  Redundancy

LFA1                     BSIK




  BC170_2.11.16
                Joins Accessing More than
                       Two Tables
        SELECT <table1~field1 table1~field2 table2~field3. . . >
          INTO (<target >)
          FROM (<table1 > INNER JOIN <table2 >
          ON <table1~keyfield1 > = <table2~keyfield1 >
             AND <table1~keyfield2 > = <table2~keyfield2 >
             AND . . .)
            INNER JOIN <table3 >
            ON <table1~keyfield > = <table3~keyfield >
             AND . . .
          WHERE . . .
        ENDSELECT.

BC170_2.11.17
                            Aliases

        SELECT A~carrname
              B~carrid
              B~connid
              B~fldate
          INTO (carrid, connid, date, carrname)
          FROM scarr AS A INNER JOIN sflight AS B
          ON scarr~carrid = sflight~carrid.
             WRITE: / carrid, connid, date, carrname.
        ENDSELECT.




BC170_2.11.18
                         Subquery
 SCARR                                 SFLIGHT




        Which airlines are not found in the sflight table?




BC170_2.11.19
                Subquery Syntax/Example

  SELECT carrid carrname FROM scarr
  INTO (scarr-carrid, scarr-carrname)
    WHERE NOT carrid IN
      ( SELECT carrid FROM sflight ).
    WRITE:/ scarr-carrid, scarr-carrname.
  ENDSELECT.




BC170_2.11.20
                        Having Clause




                List all Luftansa CONNIDs where the sum of
                          LUGGWEIGHT is > 20,000.

BC170_2.11.21
        Having Clause Syntax/Example

  SELECT carrid connid COUNT( *) SUM( luggweight )
   INTO (carrid, connid, count, sum_weight) FROM sbook
   WHERE carrid = 'LH'
   GROUP BY carrid connid
   HAVING SUM( luggweight ) > 20000.
   WRITE:/ carrid, connid, count, sum_weight.
  ENDSELECT.




BC170_2.11.22
                            Summary

  • The participants should be able to:
        – Describe how to use the following in an ABAP
          Program:
                • SELECT DISTINCT Statement
                • Dynamic WHERE Clause
                • Concatenate Statement
                • Join (Inner vs. Left Outer)
                • Aliases
BC170_2.11.23
BC170_2.11.24