Brandeis Consulting GmbH

Disclaimer

Some lines of code in this presentation were made on dry land and have never swum in the water of a project. The life jacket of a valid syntax check is therefore missing. The point is to show patterns and principles. Please forgive me if errors have crept in.

Some Examples

And 1500 lines more of this!

Another real-world example

SELECT CASE WHEN ls_tmax_ut > '000000000000000' and ls_tmax_ut < '99991231235959' THEN
CAST ( TO_VARCHAR( UTCTOLOCAL( TO_TIMESTAMP (
SUBSTRING( :ls_tmax_ut,1, 12  )
|| REPLACE(SUBSTRING( :ls_tmax_ut, 13, 2  ), '60', '59' )
), 'CET' ), 'YYYYMMDDHH24MISS' ) as DEC ( 15,0 ) )
ELSE CAST ( :ls_tmax_ut as DEC ( 15,0 ) ) END
INTO ls_tsmax_lt FROM DUMMY;

WTF!

Whats about classic Clean Code?

Jörg: List me the top 10 clean code principles

Chat GPT:
Sure! Here's a more concise version of the top 10 clean code principles:

Two different Approaches

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;

DO BEGIN
    lt_tasks =
        SELECT id, assignee, project
        FROM tasks;

    lt_tasks_with_assignee =
        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_tasks_with_assignee AS tmp
        LEFT OUTER JOIN projects AS project
            ON tmp.project = project.id;

    lt_result =
        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_result;

END;

Which one is better to debug? To Read?
Which one will use less memory? Is faster?

Two similar Queries

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

  where task.status in (1, 2)   ;
END;

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

  where task.status = 1   ;
END;

An other example

Once an Oracle Expert told me, that the NOT EXISTS predicate is slow and should be avoided. A LEFT OUTER JOINwith filtering on the NULL-Values is much faster.

We tried both approaches, but the result is the 100% exact same logical execution plan on SAP HANA!

select *
  from users as u
  where not exists (select *
                      from tasks as t
                     where u.id = t.assignee );

select u.*
  from users as u
  left outer join tasks as t
  on u.id = t.assignee
  where t.id is null;

The first statment is much more readable, because it is obvious that

• I am interested in data of the users table
• The data should be filtered

Formatting (2/2) - Consistent Formatting

When you don't want to split your expressions into smaller bits (later more about this), you should at least format them well:

    COALESCE(
        ROUND(
            TO_DECIMAL(B.ContractorSideServiceCurrency) * A.CumulatedOrderQuantity / A.TotalOrderQuantity, 2
        ) - ROUND(
            TO_DECIMAL(B.ContractorSideServiceCurrency) * (A.CumulatedOrderQuantity - A.OrderQuantity)
            / A.TotalOrderQuantity, 2
        ), 0
    ) AS ContractorSideServiceCurrency

Avoid complexity within one statement - Single Level of Abstraction

Each statement should be trivial.

Don't mix multiple operations within one statement.

• Calculation of fields with expressions like CASE or SQL-Functions
• Mapping/Renaming of all fields
• Combining tables with JOIN and the set operations like UNION
• Aggregation with GROUP BY
• Non-trivial WHERE-Conditions, e.g. with sub-queries

Use correlation names aka. table alias names - Meaningful Names

When more than a single table is queried, all tables should get an short and helpful correlation names. When you use abbreviations, explain them when they are not obvious.

  select soi.SalesOrder,
         soi.SalesOrderItem,
         soi.Product,
         so.soldToParty  
    from I_SalesOrderItem as soi 
    inner join I_SalesOrder as so 

Fields should allways be addressed by their full name: CorrelationName.fieldname
Even when they are unique!

Don't use A, B and C as correlation names!!!

Map to meaningful fieldnames (2/2) - Meaningful Names

Try to find unique fieldnames

When you have multiple DB-Tables with the same fieldname, you should try to make them unique. This reduces the problems with ambigiously defined columns. It makes it easier to use the asterisk * in the fieldlist

Let ChatGPT do the hard work!

The mapping on the previous slide was generated by ChatGPT!

kannst du bitte für diese Feldliste im SQL schöne englische Alias-Namen im Pascal-Case mit AS hinzufügen:

Avoid mentioning all fields all the time (1/2) Keep Functions Small,
Avoid Duplication (DRY)

The Problem: Statements with hundreds and more lines

    SELECT
        PurchaseOrderNumber,
        PurchaseOrderItem,
        DocumentType,
        VendorNumber,
        PurchasingGroup,
        Currency,
        DocumentDate,
        ProcessingStatus,
        MaterialNumber,

   ...

        UnitOfMeasure,
        IncotermsLocation,
        Incoterms,
        PurchaseRequisitionNumber,
        PurchaseRequisitionItem,
        DeliveryPriority,
        ConditionRecordNumber,
        ABS(ExchangeRate) AS ExchangeRate,
        CASE WHEN ExchangeRate < 0 THEN '1' ELSE '0' END AS IsInverseExchangeRate,
        ConversionNumerator,
        ConversionDenominator,
        ROUND(NetOrderValue * CASE WHEN ExchangeRate >= 0 THEN ExchangeRate ELSE -1 / ExchangeRate END, 2) AS NetOrderValue,
        NetOrderValue AS NetOrderValueCurrency,
        ROUND(ActivationValue * CASE WHEN ExchangeRate >= 0 THEN ExchangeRate ELSE -1 / ExchangeRate END, 2) AS ActivationValue,
        ActivationValue AS ActivationValueCurrency,
        ROUND(GrossPurchasePrice * CASE WHEN ExchangeRate >= 0 THEN ExchangeRate ELSE -1 / ExchangeRate END, 2) AS GrossPurchasePrice,
        GrossPurchasePrice AS GrossPurchasePriceCurrency,
        ROUND(NetPurchasePrice * CASE WHEN ExchangeRate >= 0 THEN ExchangeRate ELSE -1 / ExchangeRate END, 2) AS NetPurchasePrice,
        NetPurchasePrice AS NetPurchasePriceCurrency
    FROM :inTab

Last step of a BW routine is an outbound projection

outTab =
    SELECT
        PurchaseOrderNumber        AS EBELN,
        PurchaseOrderItem          AS EBELP,
        DocumentType               AS BSART,
        VendorNumber               AS LIFNR,
        PurchasingGroup            AS EKGRP,
        Currency                   AS WAERS,
        DocumentDate               AS BEDAT,
        ProcessingStatus           AS PROCSTAT,
        MaterialNumber             AS EMATN,
        Plant                      AS WERKS,
        UnitOfMeasure              AS MEINS,
        IncotermsLocation          AS INCO2L,
        Incoterms                  AS INCO1,
        PurchaseRequisitionNumber  AS BANFN,
        PurchaseRequisitionItem    AS BNFPO,
        Calc_DeliveryPriority      AS LPRIO,
        ConditionRecordNumber      AS KNUMV
  from :previousStep;

Avoid multiple identical expressions in the fieldlist - Avoid Duplication (DRY)

nextStep = SELECT *,
    (((ManagementFeeCurrency) * CumulatedOrderQuantity / TotalOrderQuantity) - ((ManagementFeeCurrency) * (CumulatedOrderQuantity - OrderQuantity) / TotalOrderQuantity)) AS ManagementFeeCurrency,
    (((ComplaintAllowance) * CumulatedOrderQuantity / TotalOrderQuantity) - ((ComplaintAllowance) * (CumulatedOrderQuantity - OrderQuantity) / TotalOrderQuantity)) AS ComplaintAllowance,
    (((ComplaintAllowanceCurrency) * CumulatedOrderQuantity / TotalOrderQuantity) - ((ComplaintAllowanceCurrency) * (CumulatedOrderQuantity - OrderQuantity) / TotalOrderQuantity)) AS ComplaintAllowanceCurrency,
    (((DistributionCosts) * CumulatedOrderQuantity / TotalOrderQuantity) - ((DistributionCosts) * (CumulatedOrderQuantity - OrderQuantity) / TotalOrderQuantity)) AS DistributionCosts,
    (((DistributionCostsCurrency) * CumulatedOrderQuantity / TotalOrderQuantity) - ((DistributionCostsCurrency) * (CumulatedOrderQuantity - OrderQuantity) / TotalOrderQuantity)) AS DistributionCostsCurrency,
...
FROM :previousStep

The query contains repeating parts, that can be calculated in a previous step. DRY!

previousStep = select *,
                      ( CumulatedOrderQuantity - OrderQuantity ) / TotalOrderQuantity as RemainingQuantityFactor,
                      CumulatedOrderQuantity / To talOrderQuantity                 as QuantityFactor,
                 from :inTab;

nextStep = SELECT *,
                  ManagementFeeCurrency * QuantityFactor - ManagementFeeCurrency * RemainingQuantityFactor           AS ManagementFeeCurrency,
                  ComplaintAllowance * QuantityFactor - ComplaintAllowance * RemainingQuantityFactor                 AS ComplaintAllowance,
                  ComplaintAllowanceCurrency * QuantityFactor - ComplaintAllowanceCurrency * RemainingQuantityFactor AS ComplaintAllowanceCurrency,
                  DistributionCosts * QuantityFactor - DistributionCosts * RemainingQuantityFactor                   AS DistributionCosts,
                  DistributionCostsCurrency * QuantityFactor - DistributionCostsCurrency * RemainingQuantityFactor   AS DistributionCostsCurrency,
 ...
FROM :previousStep

For complex expressions: seperate technical from business logic
Single Responsibility Principle

Step1 = select *,
               COALESCE(ROUND(TO_DECIMAL(B.FinancingCosts) * A.CumulatedOrderQuantity / A.TotalOrderQuantity, 2)
               - ROUND(TO_DECIMAL(B.FinancingCosts) * (A.CumulatedOrderQuantity - A.OrderQuantity) / A.TotalOrderQuantity, 2), 0) AS FinancingCosts,
               COALESCE(ROUND(TO_DECIMAL(B.FinancingCostsCurrency) * A.CumulatedOrderQuantity / A.TotalOrderQuantity, 2)
               -  ROUND(TO_DECIMAL(B.FinancingCostsCurrency) * (A.CumulatedOrderQuantity - A.OrderQuantity) / A.TotalOrderQuantity, 2), 0) AS FinancingCostsCurrency
                  from :inTab;

Functions like

• COALESCE or IFNULL
• TO_DECIMAL
• ROUND

were necessary for technical reasons. They add noise to the expression that obscures the clear view of the business requirement.

These sql functions can often be done in an additional previous or subsequent step without business logic.

Filter in the WHERE-Clause - Principle of Least Astonishment

Use JOIN, when you want data from the other table, use a WHERE-Clause when you want to filter!

An INNER JOIN can be used to filter. But when you don't want data from the other side, then you should use an IN-predicate or an EXISTS-predicate in the WHERE-Clause instead.

An other example of a JOIN for filtering:

select *
  from users as u
  where not exists (select *
                      from tasks as t
                     where u.id = t.assignee );

select u.*
  from users as u
  left outer join tasks as t
  on u.id = t.assignee
  where t.id is null;

Don't omit brackets in logical expressions

One of the most common SQL errors is the combination of predictes with AND and OR.

Example of missleading code

SELECT * 
  FROM Tasks
 WHERE Assignee = '4711' 
   AND Status = 1
    or Status = 2;

What is the effect?

SELECT * 
  FROM Tasks
 WHERE Assignee = '4711' 
   AND (Status = 1
     or Status = 2);

SELECT * 
  FROM Tasks
 WHERE (Assignee = '4711' 
   AND  Status = 1)
     or Status = 2;

Check of the Top 10 Clean Code Principles