Skip to Content
Technical Articles
Author's profile photo Michael Keller
Michael Keller
April 6, 2020 1 minute read

improved example of how to add semantics to a SELECT

Dear community, my last blog was about how to add more semantics to a SELECT-statement. The solution presented at that time could still be improved. As a reminder, the solution from that time.

SELECT SINGLE spld AS printer,
              spdb AS output_immediately,
              spda AS delete_from_spool
       INTO @DATA(user_print_settings)
       FROM usr01
       WHERE bname = @sy-uname.

In the solution above, it was already possible to see what information in the database table USR01 are important (printer settings). All without comment. The code tells the “story”.

Unfortunately, the solution didn’t make it clear what the meaning of database table USR01 is. This can be solved as follows. In addition, I’ve included a suggestion from Jacques Nomssi Nzali in the new solution: The user is now part of the USER_PRINT_SETTINGS structure – many thanks to him for his suggestion 🙂

  SELECT SINGLE user_master_data~bname AS user,
                user_master_data~spld  AS printer,
                user_master_data~spdb  AS output_immediately,
                user_master_data~spda  AS delete_from_spool
         INTO @DATA(user_print_settings)
         FROM usr01 AS user_master_data
         WHERE bname = @sy-uname.

With this solution it’s now clear what role the database table USR01 plays. By the way, the following solution also works.

  SELECT SINGLE @sy-uname AS user,
                user_master_data~spld AS printer,
                user_master_data~spdb AS output_immediately,
                user_master_data~spda AS delete_from_spool
         INTO @DATA(user_print_settings)
         FROM usr01 AS user_master_data
         WHERE bname = @sy-uname.

Both approaches differ only from the data element in the “USER” column. In solution 1 the column is of data element “XUBNAME”, in solution 2 it’s “SYST_UNAME”.

Important note: Jacques Nomssi Nzali made a great addition to the topic of this blog in his comment. Please read! Highly recommended. 🙂

 

Best regards, thanks for reading and please stay healthy

Michael

 

P.S.: Please support the virtual wishing well.

P.S.S.: Not tired of reading blogs? Check this blog by Marcello Urbani.

 

Assigned Tags

      2 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Jacques Nomssi Nzali
      Jacques Nomssi Nzali

      Thank you Michael for the inspiration.

      Ok, next try ?

      The DB alias is only used as documentation, so the repetition does not seem to add value. Somehow, it takes visual space away and makes the query less legible IMO. I would add it only once.

      so, my suggestion to have the easiest-to-read code would be:

          SELECT SINGLE bname AS user,
                  spld  AS printer,
                  spdb  AS output_immediately,
                  spda  AS delete_from_spool
      FROM usr01 AS user_master_data
      WHERE bname = @sy-uname
      INTO @DATA(user_print_settings).

      I have moved INTO as it must be the final clause in Cloud Platform release.

      Ok, next try ?

      Now I image how to make the code cleaner/easier to tests/easier to reuse:

      • We have a mix of DATA and TYPE declaration in the inline DATA statement. We could separate the TYPE definition
          TYPES: BEGIN OF print_settings,
             user              TYPE syuname,
             printer           TYPE rspopname,
             output_immediatly TYPE flag,
             delete_from_spool TYPE flag,
           END OF print_settings.

      I can now envision a value object that would be generated with a factory method

      CLASS lcl_user DEFINITION.
  PUBLIC SECTION.
    TYPES: BEGIN OF print_settings,
             user              TYPE syuname,
             printer           TYPE rspopname,
             output_immediatly TYPE flag,
             delete_from_spool TYPE flag,
           END OF print_settings.
             
    DATA params TYPE ts_print_settings READ-ONLY.
    
    CLASS-METHODS get_print_settings 
        IMPORTING user TYPE syuname
        VALUE(ro_user) TYPE REF TO lcl_user.
ENDCLASS.

      Now, re-using this logic would be

      DATA(mo_user_settings) = lcl_user=>get_print_settings( uname ).

      OK, there is room for improvement here. So I may rollback to the last version.

      best regards,

      JNN

      Author's profile photo Michael Keller
      Michael Keller
      Blog Post Author

      Absolutely awesome extension of the blog! 🙂 At the end of the blog, I add a note that everyone should definitely read your comment. Considering where we started and what the SELECT statement looks like now, it's fantastic.