The following open SQL statement looks a little werid, however it could really works in 740.

/wp-content/uploads/2013/12/clipboard1_338153.png

1. The field name of my structure ty_my_sflight is different from field defined in sflight, so in SQL statement I use the format <field in DB table> AS <field in my own structure> to move the content from DB to the corresponding fields of my internal table.

2. I want to calculate the percent about how many seat are occupied and put the result into my field my_seatrate. Now I could push the calculation to DB layer instead of calculating it in ABAP side.

3. The logic to determine the flight price in the example shows that we could define some application logic in open sql statement.

4. Since we are using new SQL enhanced syntax in 740, it is required that all variables defined in the application code must be escaped with flag “@” when they are being used in the SQL, as is shown in line 28 and 33.

The original data displayed in SE16:

/wp-content/uploads/2013/12/clipboard2_338154.png


The content of internal table lt_flight is listed below. We observed that the price for the 2013-2-13 and 2013-3-13 is reduced correctly, also the seat occupation percent.

/wp-content/uploads/2013/12/clipboard3_338155.png

By the way, here below is a slide from Vishal which talks about the optimization on two ERP transaction in HANA. There are several kinds of optimization categories and one of them also uses the similar syntax described in this blog:

/wp-content/uploads/2013/12/clipboard5_338760.png

The performance before HANA optimization:

/wp-content/uploads/2013/12/clipboard6_338761.png

The performance after optimization in HANA:

/wp-content/uploads/2013/12/clipboard8_338778.png


To report this post you need to login first.

6 Comments

You must be Logged on to comment or reply to a post.

  1. Ankit Sharma

    Now I could push the calculation to DB layer instead of calculating it in ABAP side.

    I think, this will make the query slow…………!!!!!!!!!!!!!

    Is nt it ????

    Well, I am not sure about it, but I think so.

    Thanking You All.

    (0) 
    1. Matthew Billingham

      It doesn’t necessarily make it slow overall – the calculation must be performed somewhere.

      While you might be able to push the calculation to the db, it’s not necessarily good programming practice. Selection of data should be decoupled from the processing logic. A little bit of transformation is ok – but not too much. Usually you have only one db server, and many appservers.

      (0) 
      1. Jerry Wang Post author

        Hello Matthew,

        Thank you very much for your comment. Yes the example here is a little misleading and not so good. I just would like to show the new functionality provided by the enhanced syntax.

        If the underlying database is not traditional one but HANA, we will gain the performance improvement by pushing the calculation to HANA layer. And also conceptually it is possible that multiple app instance can have multiple HANA instance, as is documented in HANA blue book.

        Best regards,

        Jerry

        (0) 
  2. Paul Hardy

    Do you have to put the @ sign in front of select-options as in

    SELECT * FROM sflight

      INTO CORRESPONDING FIELDS OF lt_sflight

      WHERE carrid IN @s_carrid.

    ?

    Every example I have seen glosses over this by using hard coded selection criteria which is not very realistic.

    My guess would be not, you could still use S_CARRID on it’s own, as otherwise that would stuff up backwards compatability, but I am interested to know either way.

    Cheersy Cheers

    Paul

    (0) 

Leave a Reply