Working with Expressions in ABAP SQL

Objective

After completing this lesson, you will be able to use some simple expressions in ABAP SQL

Literals

Literals and Constants in ABAP SQL

The simplest expressions are literals. Just like ABAP itself, ABAP SQL knows two kinds of literals: text literals, specified in simple quotes, and number literals, which can be positive or negative whole numbers. The type of literals in ABAP SQL is the same as in ABAP: Text literals are of type C and number literals are of type I.

In the example, the first element of the field list is a text literal, whereas the second and third element are number literals, one with a positive value and the other with a negative value.

Note

String literals, specified in back-quotes, are not available in ABAP SQL.

Instead of using literals directly, you are recommended to define constants and use them inside your ABAP SQL statement. This improves readability and gives you access to more types than just C and I. When you use a constant in an ABAP SQL statement, the prefix @ is mandatory.

In the example, the fourth element of the element list is a constant of name C_NUMBER of type I and value 1234.

Try It Out: Literals

  1. Create a new global class that implements interface IF_OO_ADT_CLASSRUN.
  2. Copy the following code snippet to the implementation part of method if_oo_adt_classrun~main( ):
    Code Snippet
    123456789101112131415161718
    
 CONSTANTS c_number TYPE i VALUE 1234.

    SELECT FROM /dmo/carrier
         FIELDS 'Hello'    AS Character,    " Type c
                 1         AS Integer1,     " Type i
                -1         AS Integer2,     " Type i

                @c_number  AS constant      " Type i  (same as constant)

          INTO TABLE @DATA(result).

    out->write(
      EXPORTING
        data   = result
        name   = 'RESULT'
    ).
  3. Select CTRL + F3 to activate the class and F9 to execute it as a console app.
  4. Analyze the console output.
  5. Debug the execution of method if_oo_adt_classrun~main( ) and analyze the type of columns CHARACTER, INTEGER1, and INTEGER2 of internal table RESULT.
  6. Add more literals to the FIELDS list to get familiar with the syntax.

The CAST Expression

Unlike ABAP itself, ABAP SQL does not know implicit type conversions. The CAST expression of CDS allows you to implement type conversions explicitly.

The operand for a type conversion can not only be a literal or a field of the data source. All kinds of other expressions are possible like arithmetic expressions, predefined functions, and many others.

You can specify most predefined dictionary types as target types, for example INT4, CHAR, DEC, FLTP, DATS and so on. In ABAP SQL, it is not possible to use Dictionary Data elements as target types.

Note

There are restrictions with regard to the combination of source type and target type. Some combinations are not supported at all. Others only work with limitations and can lead to runtime errors. A detailed matrix can be found in the ABAP language documentation.

Predefined Types in ABAP Dictionary

When defining types in the ABAP Dictionary, for example a data element, a specific set of predefined types is used. When such a Dictionary type is used to type a data object in ABAP, the Dictionary type is mapped to a specific predefined ABAP type. Dictionary type CHAR, for example, is mapped to ABAP type C, type INT to type I, type FLTP to type F, type DEC to P and so on.

Some Dictionary types do not have a direct counterpart in ABAP. Dictionary type MANDT, for example, is technically identical to type CHAR(3), but has a special meaning when used for database table fields. In ABAP, where this special meaning is not required, type MANDT is mapped to type C LENGTH 3. Similarly, Dictionary types UNIT and CUKY are mapped to ABAP type C and QUAN and CURR to ABAP type P.

The length for Dictionary types DEC, QUAN, and CURR is specified as number of digits. The length for ABAP type P is specified as number of bytes. n bytes correspond to 2 *n - 1 digits.

Examples of CAST Expressions

In this example, the same text literal is converted to different compatible target types. Pay attention to the following facts:

  • The conversion to numeric types (INT4, DEC, FLTP) leads to runtime errors if the source literal contains non-digit characters.
  • The conversion to type DATS does not check whether the 8 digits form a valid date.
  • Type CHAR( 4 ) is a compatible type but the conversion results in information loss.

Note

The fact that only one decimal place is displayed in column DEC_10_2 is an artifact of the UI.

Try It Out: The CAST Expression

  1. Create a new global class that implements interface IF_OO_ADT_CLASSRUN.
  2. Copy the following code snippet to the implementation part of method if_oo_adt_classrun~main( ):
    Code Snippet
    1234567891011121314151617181920
    
    SELECT FROM /dmo/carrier
         FIELDS '19891109'                           AS char_8,
                CAST( '19891109' AS CHAR( 4 ) )      AS char_4,
                CAST( '19891109' AS NUMC( 8  ) )     AS numc_8,

                CAST( '19891109' AS INT4 )          AS integer,
                CAST( '19891109' AS DEC( 10, 2 ) )  AS dec_10_2,
                CAST( '19891109' AS FLTP )          AS fltp,

                CAST( '19891109' AS DATS )          AS date

           INTO TABLE @DATA(result).

    out->write(
      EXPORTING
        data   = result
        name   = 'RESULT'
    ).
  3. Select CTRL + F3 to activate the class and F9 to execute it as a console app.
  4. Analyze the console output.
  5. Debug the execution of method if_oo_adt_classrun~main( ) and analyze the type of the various columns of internal table RESULT.
  6. Add more CAST expressions to the FIELDS list to get familiar with the syntax.

CASE Distinctions

ABAP SQL offers case distinctions that you can used in the element list of a SELECT statement and as operands for other expressions.

A case distinction returns exactly one value. This value depends on a series of conditions. A case distinction always starts with key word CASE and ends with key word END. The rest depends on the type of the case distinction.

A Simple Case Distinction corresponds to ABAP control structure CASE … ENDCASE. It compares the value of an operand (operand in the example) with a series of other operands. You can use any SQL expression as operand, for example, table fields, literals, constants, ABAP variables, SQL expressions, and so on.

A Complex Case Distinction, also known as Searched Case, corresponds to ABAP control structure IF … ENDIF with its optional parts ELSEIF and ELSE. It evaluates the SQL conditions after keywords WHEN in the given sequence. The first condition that is true determines the result of the CASE expression. If none of the conditions are true, the result is determined by the ELSE addition.

Simple Case Distinction
The result depends on the values of several operands. The simple case distinction is comparable to the CASE statement in ABAP.
Complex Case Distinction
Also known as searched distinction. The result depends on a sequence of logical expressions. The complex case distinction is comparable to the IF statement in ABAP.

This example for a simple case uses the content of field TITLE to return a longer text as a field TITLE_LONG. Where TITLE contains value "Mr.", column TITLE_LONG contains value "Mister". Where TITLE contains 'Mrs.', column TITLE_LONG contains 'Misses'. For any other value in TITLE, TITLE_LONG contains blanks.

This example for a complex case uses a series of SQL conditions to determine a value for a new column BOOKING_STATE.

If the value of column SEATS_OCCUPIED is less than the value of column SEATS_MAX, the new column returns a text indicating that more seats are available. Only if that is not the case, the next condition is checked, and so on. If none of the conditions are true, column BOOKING_STATE contains the value specified after keyword ELSE.

Try It Out: CASE distinctions

  1. Create a new global class that implements interface IF_OO_ADT_CLASSRUN.
  2. Copy the following code snippet to the implementation part of method if_oo_adt_classrun~main( ):
    Code Snippet
    123456789101112131415161718192021222324252627282930313233343536373839404142
    
    SELECT FROM /dmo/customer
         FIELDS customer_id,
                title,
                CASE title
                  WHEN 'Mr.'  THEN 'Mister'
                  WHEN 'Mrs.' THEN 'Misses'
                  ELSE             ' '
               END AS title_long

        WHERE country_code = 'AT'
         INTO TABLE @DATA(result_simple).

    out->write(
      EXPORTING
        data   = result_simple
        name   = 'RESULT_SIMPLE'
    ).

**********************************************************************

    SELECT FROM /DMO/flight
         FIELDS flight_date,
                seats_max,
                seats_occupied,
                CASE
                  WHEN seats_occupied < seats_max THEN 'Seats Avaliable'
                  WHEN seats_occupied = seats_max THEN 'Fully Booked'
                  WHEN seats_occupied > seats_max THEN 'Overbooked!'
                  ELSE                                 'This is impossible'
                END AS Booking_State

          WHERE carrier_id    = 'LH'
            AND connection_id = '0400'
           INTO TABLE @DATA(result_complex).

    out->write(
      EXPORTING
        data   = result_complex
        name   = 'RESULT_COMPLEX'
    ).
  3. Select CTRL + F3 to activate the class and F9 to execute it as a console app.
  4. Analyze the console output.
  5. Play around with the CASE expressions to get familiar with the syntax.

Log in to track your progress & complete quizzes

Learning

SAP FacebookSAP YoutubeSAP LinkedIn