SQLScript basics

Declarative programming

(C) Brandeis Consulting.

Declarative vs. imperative programming

Declarative (SQLScript)

  • The result is described
  • All steps are based on tables
  • Only SELECT statements and table variables are allowed
  • There are no branches in the programme flow.

Imperative (SQL or ABAP)

  • An algorithm is specified statement by statement
  • Data processing takes place line by line and field by field
  • The programme flow is controlled depending on the data
(C) Brandeis Consulting.

Example of bad ABAP coding

 FIELD-SYMBOLS: <ls_data> TYPE dtfitx_4.
  
  LOOP AT c_c_t_data ASSIGNING <ls_data>.
    SELECT SINGLE bupla gsber FROM bseg
      INTO (<ls_data>-bupla,<ls_data>-gsber)
      WHERE bukrs = <ls_data>-bukrs
        AND belnr = <ls_data>-belnr
        AND gjahr = <ls_data>-gjahr
        AND buzei = <ls_data>-buzei.
  ENDLOOP.

This led to runtime problems. So there was a refactoring....

(C) Brandeis Consulting.

Example of better ABAP coding

TYPES: BEGIN OF ty_bseg,
          bukrs TYPE bukrs,
          belnr TYPE belnr_d,
          gjahr TYPE gjahr,
          buzei TYPE buzei,
          gsber TYPE gsber,
          bupla TYPE bupla,
        END OF ty_bseg.
DATA: lt_bseg_sel TYPE STANDARD TABLE OF ty_bseg 
                      WITH DEFAULT KEY,
      ls_bseg TYPE ty_bseg,
      lth_bseg TYPE HASHED TABLE OF ty_bseg
                WITH UNIQUE KEY bukrs belnr gjahr buzei.
LOOP AT c_c_t_data ASSIGNING FIELD-SYMBOLS(<ls_data>).
  ls_bseg-bukrs = <ls_data>-bukrs.
  ls_bseg-belnr = <ls_data>-belnr.
  ls_bseg-gjahr = <ls_data>-gjahr.
  ls_bseg-buzei = <ls_data>-buzei.
  APPEND ls_bseg TO lt_bseg_sel.
ENDLOOP.
SORT lt_bseg_sel.
DELETE ADJACENT DUPLICATES FROM lt_bseg_sel.

SELECT bukrs belnr gjahr buzei gsber bupla
        FROM bseg
        INTO TABLE lth_bseg
        FOR ALL ENTRIES IN lt_bseg_sel
        WHERE bukrs = lt_bseg_sel-bukrs
          AND belnr = lt_bseg_sel-belnr
          AND gjahr = lt_bseg_sel-gjahr
          AND buzei = lt_bseg_sel-buzei.

LOOP AT c_c_t_data ASSIGNING <ls_data>.
  read TABLE lth_bseg ASSIGNING FIELD-SYMBOLS(<s_bseg>)
              with TABLE KEY bukrs = <ls_data>-bukrs
                            belnr = <ls_data>-belnr
                            gjahr = <ls_data>-gjahr
                            buzei = <ls_data>-buzei.
  if sy-subrc = 0.
    <ls_data>-bupla = <s_bseg>-bupla.
    <ls_data>-gsber = <s_bseg>-gsber.
  endif.
ENDLOOP.

But still declared step by step. Can therefore not be parallelised.

(C) Brandeis Consulting.

Example of the same logic in SQLScript

outtab = SELECT it.*,
                bseg.bupla,
                bseg.gsber
         FROM :intab as it
           LEFT OUTER JOIN bseg
           ON it.bukrs = bseg.bukrs
           AND it.belnr = bseg.belnr
           AND it.gjahr = bseg.gjahr
           AND it.buzei = bseg.buzei;
(C) Brandeis Consulting.

Flipchart / Whiteboard

Comparison of transformation routine declarative (SQLScript) and imperative (ABAP)

(C) Brandeis Consulting.

Approach 1: A query in a SELECT statement

DO BEGIN  
  SELECT task.id, 
          task.assignee,
          task.project,
          assignee.firstname AS as_firstname,
          assignee.lastname AS as_surname,
          project.id AS p_id,
          pm.id AS pl_id,
          pm.firstname AS pm_firstname,
          pm.lastname AS pm_lastname
  
  FROM tasks AS task
  
  LEFT OUTER JOIN users AS assignee
  ON task.assignee = assignee.id
  
  LEFT OUTER JOIN projects AS project
  ON task.project = project.id
  
  LEFT OUTER JOIN users AS pm
  ON project.project_manager = pm.id ;    
END;

How can we break this down into smaller steps using table variables?

(C) Brandeis Consulting.

Approach 2: Read DB tables in table variables in advance

DO BEGIN
 lt_tasks = SELECT id AS tasks_id, assignee, project FROM tasks;
 lt_user = SELECT id AS user_id, firstname, lastname FROM users;
 lt_projects = SELECT id AS project_id, project_manager FROM projects;
 
 SELECT *
 FROM :lt_tasks AS a
     
 LEFT OUTER JOIN :lt_user as assignee
 ON a.assignee = assignee.user_id
     
 LEFT OUTER JOIN :lt_projects as project
 ON a.project = project.project_id
     
 LEFT OUTER JOIN :lt_user as pm
 ON project. project_manager = pm.user_id;
     
END;
(C) Brandeis Consulting.

Approach 3: Step by step

DO BEGIN
 lt_tasks = SELECT id, assignee, project FROM tasks;
 
 lt_with_user = SELECT tasks.*,
                       assignee.firstname, 
                       assignee.lastname
                FROM :lt_tasks AS tasks
                LEFT OUTER JOIN users as assignee
                ON tasks.assignee = assignee.id;
                     
 lt_with_project = SELECT tmp.*,
                          project.id AS projektid,
                          project.project_manager
                   FROM :lt_with_user AS tmp
                   LEFT OUTER JOIN projects AS project
                   ON tmp.project = project.id;
 lt_complete = SELECT tmp.*,
                      pm.firstname as PL_firstname,
                      pm.lastname as PL_surname
               FROM :lt_with_project AS tmp
               LEFT OUTER JOIN users AS pm
               ON pm.id = tmp.project_manager;
              
 SELECT * FROM :lt_complete;
END;
(C) Brandeis Consulting.

Comparison of the approaches

Which approach

  • requires the most memory (ram)?
  • is the fastest?
  • is the most readable?
  • is best suited for debugging?
(C) Brandeis Consulting.