CubeServ Blog
Stay up-to-date on the data-driven business with tools for analytics from SAP & Co. and do not miss any news, downloads & events.

SQLscript solution pattern

Been looking for it for a long time? Now you’ve found it!

Our overview of typical problems and solutions in the area of HANA SQLscript.

The solution patterns range from purely linguistic problems (e.g. “Which language element do I use to determine the first entry”) to formal problems (e.g. “How do I convert time characteristics in SQLscript”) and application-related requirements (e.g. “How do I look up master data in SQLscript”).

The sample solutions do not claim to be the sole or best solution to a problem but are intended to serve as a template and inspire you with different approaches. Your feedback on improvements, alternatives, and additions is always welcome!

The coding example is formulated as it might typically occur in an AMDP routine. However, this formulation cannot be used in a HANA Studio SQL window or in the SQL editor of the ABAP transaction DBACOCKPIT (as it is not defined there what the inTab should be). A coding example is therefore also given in some places, which could be used in the SQL window. These examples contain additional lines that build up the sample data.

Initial values

Description

Initialize character-type columns with ”, numbers with 0.

Coding example

outtab =
SELECT
'' AS "/BIC/STRASSE",
0  AS "/BIC/HAUSNR"
FROM :intab; 

A similar formulation generates sample data that can then be used further.

Coding example SQL-Editor

SELECT 'A' AS "SPALTE_A",
       'B' AS "SPALTE_B", 
       1.5 AS "BETRAG" 
     FROM DUMMY; 

Temporary tables (table variables)

Description

Tables are defined dynamically at runtime when they are filled with a select.

Note

Table variables do not have to be declared before use. However, the name must be preceded by a “:” in subsequent use.

Coding example

tmptab  = SELECT * FROM :intab;
tmptab2 = SELECT * FROM :tmptab;
outtab  = SELECT * from :tmptab2;
 

Table variables are an example of where SQL differs from SQLscript. These variables are part of the SQLscript language, but not of the SQL language.

In order to be able to use them in an SQL window, a so-called anonymous block must be inserted.

The last SELECT in the following example then generates the output of the result of the SQL window. Without this line, the statement is executed, but there is no result. Several such SELECTs at the end also generate several result windows in the SQL editor of the HANA Studio.

Coding example SQL window

DO
BEGIN
tmptab = SELECT 'A' AS "SPALTE_A", 
                'B' AS "SPALTE_B", 
                1.5 AS "BETRAG" 
            FROM DUMMY;
tmptab2 = SELECT "SPALTE_A" FROM :tmptab;
SELECT * FROM :tmptab2; -- Tabelle als Resultat ausgeben
END; 

Renaming columns

Description

One or more columns are to be given a different name.

Coding example

outtab = 
  SELECT "ALTER_SPALTENNAME" AS "NEUER_SPALTENNAME" FROM :inTab; 

Coding example SQL window

DO
BEGIN
tab1 = SELECT 'A' AS "SPALTE_A", 
              'B' AS "SPALTE_B", 
              1.5 AS "BETRAG" 
             FROM DUMMY;
SELECT * FROM :tab1; -- Resultat Tabelle mit SPALTE_A
SELECT "SPALTE_A" AS "NEUE_SPALTE" FROM :tab1; -- Resultat umbenannt
END; 

Union of two structurally identical tables

Description

The union of two tables is formed. The tables must have an identical structure.

Coding example

outtab = 
  SELECT columns FROM :TAB1
  
  UNION ALL
  
  SELECT columns FROM :TAB2; 

Coding example SQL window

DO
BEGIN
tab1 = SELECT 'A' AS "SPALTE_A", 
              'B' AS "SPALTE_B", 
              1.5 AS "BETRAG" 
             FROM DUMMY;
tab2 = SELECT * FROM :tab1; -- 2. Tabelle mit Daten
tab3 = SELECT * FROM :tab1 
       UNION ALL 
       SELECT * FROM :tab2;
SELECT * FROM :tab3; -- Resultat ausgeben
END; 

Join two tables

Description

The join of two tables is formed.

Coding example

outtab = 
  SELECT T1.*, T2.* FROM :TAB1 AS T1
    LEFT OUTER JOIN :TAB2 AS T2
    ON T1.SPALTE_A = T2.SPALTE_X AND
       T1.SPALTE_B = T2.SPALTE_Y;
   

Coding example SQL window

DO
BEGIN
tab1 = SELECT 'A' AS "SPALTE_A", 
              'B' AS "SPALTE_B", 
              1.5 AS "BETRAG" 
             FROM DUMMY;
tab2 = SELECT 'A' AS "SPALTE_X", 
              'C' AS "SPALTE_Y", 
              2 AS "MENGE" 
             FROM DUMMY;
tab3 = SELECT T1.*, T2.* from :tab1 AS T1
         LEFT OUTER JOIN :tab2 AS T2
         ON T1."SPALTE_A" = T2."SPALTE_X";
SELECT * FROM :tab3; -- Resultat ausgeben
END; 

Case distinction

Description

You can already make a case distinction in the SELECT statement in the column to be determined with the help of CASE.

Coding example

outtab = 
SELECT
CASE BUCHUNGSART
  WHEN 'HABEN' THEN BETRAG
  WHEN 'SOLL'  THEN -1 * BETRAG
  ELSE 0 
END AS BETRAG_VZ
FROM :intab; 

This statement is equivalent to the following version:

outtab = 
SELECT
CASE 
  WHEN BUCHUNGSART = 'HABEN' THEN BETRAG
  WHEN BUCHUNGSART = 'SOLL'  THEN -1 * BETRAG
  ELSE 0 
END AS BETRAG_VZ
FROM :intab; 

In the 2nd formulation, you have more freedom to formulate more complex conditions in the WHEN condition, e.g:

outtab = 
SELECT
CASE 
  WHEN BUCHUNGSART = 'HABEN' AND 
       NOT ( STORNOFLAG = 'X' )
       THEN BETRAG
  WHEN BUCHUNGSART = 'HABEN' AND 
       STORNOFLAG = 'X'
       THEN 0
  WHEN BUCHUNGSART = 'SOLL'  THEN -1 * BETRAG
  ELSE 0 
END AS BETRAG_VZ
FROM :intab; 

Coding example SQL window

DO
BEGIN
tab1 = SELECT 'HABEN' AS "BUCHUNGSART", 1.5 AS "BETRAG" FROM DUMMY
       UNION ALL
       SELECT 'SOLL'  AS "BUCHUNGSART", 2.5 AS "BETRAG" FROM DUMMY;
tab2 = SELECT "BUCHUNGSART", "BETRAG",
       CASE 
         WHEN "BUCHUNGSART" = 'HABEN' THEN "BETRAG"
         WHEN "BUCHUNGSART" = 'SOLL'  THEN -1 * "BETRAG"
         ELSE 0
       END AS "BETRAG_VZ"
      FROM :tab1;
SELECT * FROM :tab2; -- Resultat ausgeben
END; 

Define, fill and use variables

Description

You can define local variables. These can be filled with SELECT statements, but their content can also be read out again using SELECT or they can be used in CASE statements.

Coding example

DECLARE lv_tvarvc STRING;

SELECT DISTINCT LOW INTO lv_tvarvc
  FROM "TVARVC" 
  WHERE "NAME" = 'my_setting';
  
outtab = 
  SELECT :lv_tvarvc AS TVARVC, * FROM intab; 

Coding example SQL window

DO
BEGIN
DECLARE lv_tvarvc STRING;
tab1 = SELECT 'A' AS "SPALTE_A", 
              'B' AS "SPALTE_B", 
              1.5 AS "BETRAG" 
             FROM DUMMY;
SELECT 'Value1' INTO lv_tvarvc FROM DUMMY;
SELECT :lv_tvarvc, * FROM :tab1; -- Resultat ausgeben
END; 

Avoid NULL values

Description

If, for example, no matching hits are found in a left outer join in the joined table, the selected columns of this table contain NULL values as a result. These values are quite unpleasant, e.g. because they do not correspond to an initial value in ABAP. Therefore, such NULL values should always be intercepted with the COALESCE function. COALESCE(A,B) returns the value A if A is not NULL, otherwise B. This means that you can always use B to specify a substitute value to be returned instead of NULL.

Alternatively, you can also work with the IFNULL function.

Note

A coding example can be found in the section Date calculation.

Count number of lines

Description

You can use the COUNT operator to count the rows. However, you can also determine how often characteristics occur in each case, e.g. whether only once or several times.

Here we only give an example for the SQL console, the syntax is then easily transferable to use cases in AMDP.

Coding example SQL window

DO
BEGIN
DECLARE lv_anz_zeilen INT;
tab1 = SELECT 'A' AS "SPALTE_A", 
              'B' AS "SPALTE_B", 
              1.5 AS "BETRAG" 
             FROM DUMMY
       UNION
       SELECT 'A' AS "SPALTE_A", 
              'C' AS "SPALTE_B", 
              2.5 AS "BETRAG"
            FROM DUMMY;
               
SELECT COUNT( * ) INTO lv_anz_zeilen FROM :tab1; -- Gesamtzahl als
                                                 -- lokale Variable

tab2 = SELECT COUNT ( * ) AS "COUNTER" FROM :tab1; -- Gesamtzahl als Tabelle

SELECT :lv_anz_zeilen AS "Gesamtzahl", 
       COUNT(*) AS "Anzahl pro Merkmal SPALTE_A", 
       "SPALTE_A" FROM :tab1
  GROUP BY "SPALTE_A";  -- Ausgabe 1, auch wieviele Ausprägungen SPALTE_A
  
SELECT * from :tab2;  -- Ausgabe 2
              
END; 

Conditions

Description

Conditions can be formulated with the help of IF.

Note

Surprisingly often, IF statements can be replaced by CASE statements in SELECT. These are better suited to processing the data en masse. IF is often used to look at individual cases, which should be avoided in order to achieve good performance.

Coding example

DO
BEGIN
DECLARE lv_anz_zeilen INT;
tab1 = SELECT 'A' AS "SPALTE_A", 
              'B' AS "SPALTE_B", 
              1.5 AS "BETRAG" 
             FROM DUMMY
       UNION
       SELECT 'A' AS "SPALTE_A", 
              'C' AS "SPALTE_B", 
              2.5 AS "BETRAG"
            FROM DUMMY;
               
SELECT COUNT( * ) INTO lv_anz_zeilen FROM :tab1; -- Gesamtzahl

IF :lv_anz_zeilen > 0 THEN

  SELECT 'Tabelle ist nicht leer' AS "TEXT" from DUMMY;

ELSE

  SELECT 'Tabelle ist leer' AS "TEXT" from DUMMY;
              
END IF;

END; 

Sum, Max, Min, ...

Description

Aggregations such as summation, maximum of a column, etc. can again be created directly in the SELECT statement.

Note

The columns over which the aggregate function is formed must be in a GROUP-BY clause. In general, these are all columns except those on which the sum, maximum, or similar is to be formed.

Coding example

DO
BEGIN

tab1 = SELECT 'A' AS "SPALTE_A", 
              'B' AS "SPALTE_B", 
              1.5 AS "BETRAG" 
             FROM DUMMY
       UNION
       SELECT 'A' AS "SPALTE_A", 
              'C' AS "SPALTE_B", 
              2.5 AS "BETRAG"
            FROM DUMMY;
               
               
SELECT "SPALTE_A", MAX( "BETRAG" ) AS MAX FROM :tab1
  GROUP BY "SPALTE_A"; -- Max pro Ausprägung SPALTE_A

SELECT "SPALTE_B", MAX( "BETRAG" ) AS MAX FROM :tab1
  GROUP BY "SPALTE_B"; -- Max pro Ausprägung SPALTE_B

END; 

Replace leading 0s

Description

Leading 0s can be replaced using the LTRIM operator.

Note

The LTRIM operator can also be used to eliminate leading spaces, for example.

Coding example

SELECT LTRIM( BPARTNER, '0') AS BP FROM :intab; 

Coding example SQL window

SELECT LTRIM( '00001234', '0') AS BP FROM DUMMY; 

Check for digits

Description

You can check columns to see whether they only contain digits (e.g. date fields in internal ABAP format).

Note

In the example, the data records that contain something that is not a digit are selected and marked.

Coding example

SELECT DATUM, 'X' AS MARKIERUNG FROM :intab
  WHERE DATUM LIKE_REGEXPR '[^0-9]'; 

Condensing spaces

Description

You can remove leading, trailing, and center spaces by using the very powerful REPLACE_REGEX operator.

Coding example

SELECT REPLACE_REGEXPR ('[\s]' in 'ABC DEF') FROM DUMMY; 

Remove invalid characters

Description

You can remove characters that are invalid for BW by using the very powerful REPLACE_REGEX operator in combination with an expression that removes all non-printable characters from a text.

The first coding variant removes all non-printable characters, which unfortunately also include German umlauts, for example. If this is not desired, the second variant only removes certain invisible control characters plus the character for protected spaces (Hex A0).

Coding example

SELECT REPLACE_REGEXPR( '([^[:print:]])' IN 'TEXT' WITH '' OCCURRENCE ALL ) FROM DUMMY;

SELECT REPLACE_REGEXPR( '([[:cntrl:]\x{00A0}])' IN 'TEXT' WITH '' OCCURRENCE ALL ) FROM DUMMY; 

Determine first data set

Description

MAX or MIN functions can be used to determine the newest or oldest data. However, you can also determine the first data record of a certain series, e.g. the BusinessPartner with the earliest change date.

Note

Similarly, you can of course also determine the last data record with LAST_VALUE.

Coding example

SELECT FIRST_VALUE( BPARTNER ORDER BY CHANGED_ON ) FROM :intab;

SELECT BPARTNER, LAST_VALUE ( AMOUNT ORDER BY CHANGED_ON ) AS AMOUNT FROM :intab 
  GROUP BY BPARTNER; 

Determine rank

Description

You can use the RANK function to assign a rank number to data. This is a type of sort number but has the advantage that the data does not have to be sorted or re-sorted.

Note

This can be used, for example, to determine the last position for a document.

Coding example

tab1 = SELECT *, 
         RANK() OVER ( PARTITION BY intab."BELEGNUMMER" ORDER BY intab."POSITION" DESC ) 
         AS ZEILENNUMMER_ABSTEIGEND 
         FROM :intab;
         
tab2 = select * from :tab1
         where ZEILENNUMMER_ABSTEIGEND = 1; 

Access to previous or subsequent lines

Description

If you want to compare rows in a table with the previous or subsequent row, this often leads to a cursor being opened and a loop being programmed. This is possible in principle, but it contradicts the paradigm of processing the data as massively as possible in order to achieve optimum performance.

One possibility would be to join the table with itself offset by one row, which would allow a comparison within one (then twice as wide) row.

However, HANA SQLscript offers a way to access the corresponding rows with the LEAD and LAG functions.

Note

The example lists the clients of a system. The first column contains the normal list, the second column uses Lead to access the next row in the table and Lag to access the previous row.

Coding example

select MANDT, 
       LEAD( MANDT ) OVER ( ORDER BY MANDT ) AS LEAD, 
       LAG( MANDT )  OVER ( ORDER BY MANDT ) AS LAG 
  FROM T000;
  

Outcome

blank

Date calculation

Description

The other characteristics (week, month, year,…) are to be derived from the calendar day.

Note

In HANA, the timetables must be filled once (→ Modeler perspective), otherwise the tables are empty.
Alternatively, the tables can also be updated with MDX commands such as
MDX UPDATE TIME DIMENSION … or MDX UPDATE FISCAL CALENDAR …
can be used. Pay attention to what the finest granularity should be (e.g. day or second).
If nothing is found, the system falls back to 01.01.2020 (this was a Wednesday, weekday = 3, hence this value).

Coding example

-- UDATE bitte durch das entsprechende Zeitmerkmal ersetzen

SELECT

  COALESCE( I.UDATE, '20200101' ) AS CALDAY,

  COALESCE( TO_NVARCHAR( T.DAY_OF_WEEK_INT + 1 ), '3' ) AS WEEKDAY1,

  COALESCE( T.CALMONTH, '01' ) AS CALMONTH,

  COALESCE( T.CALWEEK, '202001' ) AS CALWEEK,

  COALESCE( T.MONTH, '01' ) AS CALMONTH2,

  COALESCE( T.CALQUARTER, '20201' ) AS CALQUARTER,

  COALESCE( TO_NVARCHAR( T.QUARTER_INT ), '1' ) AS CALQUART1,

  CASE 
    WHEN COALESCE( T.QUARTER_INT, 1 ) = 1 OR 
         COALESCE( T.QUARTER_INT, 1 ) = 2 THEN
      '1'
    ELSE
      '2'
  END AS HALFYEAR1,

  COALESCE( T.YEAR, '2020' ) AS CALYEAR,

FROM :intab AS I

LEFT OUTER JOIN "_SYS_BI"."M_TIME_DIMENSION" AS T

    ON I.UDATE = T.DATE_SAP; 

Derivation of a current UTC timestamp

Description

The current UTC timestamp is to be determined in an AMDP routine.

Note

Example coding for an AMDP field procedure.

Coding example

METHOD PROCEDURE BY DATABASE PROCEDURE FOR HDB LANGUAGE SQLSCRIPT OPTIONS READ-ONLY.

outtab =
SELECT to_nvarchar(current_utctimestamp, 'YYYYMMDDHH24MISS') AS "ETL_TIMESTAMP",
record,
sql__procedure__source__record
FROM :intab;

errortab =
SELECT '' AS "ERROR_TEXT" ,
'' AS "SQL__PROCEDURE__SOURCE__RECORD"
FROM dummy
WHERE 0 = 1;

ENDMETHOD. 

Derivation of the Request TSN

Description

In BW on HANA, the AMDP interface does not provide the request number. It has to be derived somewhat laboriously in order to be able to store this number in a corporate memory, for example (recommended, as you want to reload individual requests and this field is not available as a selection in the DTP in the standard system).

Note

Example AMDP field routine.
The target ADSO must be specified in the coding.

Coding example

METHOD PROCEDURE BY DATABASE PROCEDURE FOR HDB LANGUAGE SQLSCRIPT 
OPTIONS READ-ONLY using RSPMREQUEST.


declare lv_reqtsn varchar(23);

SELECT request_tsn INTO lv_reqtsn FROM rspmrequest
where datatarget = '' AND
request_status = 'Y' AND
last_operation_type = 'C' AND
request_is_in_process = 'Y' AND
storage = 'AQ';

outtab =
SELECT lv_reqtsn AS "ETL_REQTSN",
record,
sql__procedure__source__record

FROM :inTab as input;

errortab =
SELECT '' AS "ERROR_TEXT" ,
'' AS "SQL__PROCEDURE__SOURCE__RECORD"
FROM dummy
WHERE 0 = 1;

ENDMETHOD. 

Latest data from a wo-ADSO

Description

When you read data from a write-optimized ADSO (wo-ADSO), it is often necessary to read the latest status of this data, i.e. the latest request. This is easily done by searching for the maximum request number.

Coding example

SELECT T1.REQTSN, T1.KEY, T1.FIELD
  FROM WO-ADSO as T1
    JOIN ( SELECT MAX(REQTSN) as TSN, KEY FROM WO-ADSO GROUP BY KEY ) AS T2 
    ON T1.REQTSN = T2.TSN AND
       T1.KEY = T2.KEY
  WHERE T1.KEY = ...;
 

Reading master data

Description

You can read the master data of an InfoObject by adding the P table (for time-dependent master data, the Q table) of the InfoObject.

Note

If referential integrity is given (i.e. master data is guaranteed to exist for the transaction data), then an INNER JOIN can be used. This is more efficient than an OUTER JOIN.
Otherwise, a LEFT OUTER JOIN should be used and any occurrence of NULL values should be intercepted by COALESCE.

Coding example

SELECT T1.*,
       COALESECE( T2.FIELD, '') AS FIELD
  FROM ADSO as T1
    LEFT OUTER JOIN /BIC/POBJECT AS T2
    ON T2.KEY = T1.OBJECTFIELD
  WHERE T2.OBJVERS = 'A' AND ...;
 

Catch SQL errors in AMDP

Description

If SQL errors occur in the AMDP, these can be intercepted and returned to the DTP as error messages.

Note

The following code should then be placed at the beginning of the AMDP script.

Coding example

DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN 
  outtab = SELECT TOP 0 * from :outtab;
  errortab = SELECT 'My Error Message' AS ERROR_TEXT, 
                    '' AS SQL__PROCEDURE__SOURCE__RECORD FROM DUMMY'; 
END;
 

Throw own SQL error messages in AMDP

Description

You can also throw your own error messages, which then appear both in the log of the DTP and as SQL error messages in the trace files of the index server of the HANA DB.

Note

The following code should then be placed at the beginning of the AMDP script.

Coding example

DECLARE MYCOND CONDITION FOR SQL_ERROR_CODE 10001;
DECLARE EXIT HANDLER FOR MYCOND RESIGNAL;
SIGNAL MYCOND SET MESSAGE_TEXT = '     Typical Not-found Error';
   


-- in case of error throw here
SIGNAL MYCOND;
 

Post valid records in AMDP, write incorrect ones to error stack

Description

If you are in a BW/4HANA release, the setting “Allow error handling for HANA routines” must be set in the transformation as a prerequisite. If this is set, the AMDP interface defines an errorTab table in addition to the outTab table.
A suitable setting must also be made in the DTP, e.g. the setting “Set request to green, write error stack, update valid records”.

Note

The following code shows an example of how records are read from a master data table. Those records for which a non-empty entry is found are treated as valid records, the others are treated as incorrect records.

Coding example

table1 = SELECT
       :i_req_requid as ZTS1REQTSN, 
       CALYEAR,
       COUNTRY,
       PRODUCT,
       I.RECORDMODE,
       I."/BIC/ZTS1CNTRY",
       COALESCE(P."/BIC/ZTS1FAREA",'') AS "/BIC/ZTS1FAREA",
       to_nvarchar(current_utctimestamp, 'YYYYMMDDHH24MISS') AS UTC,
       QUANTITY,
       RECORD,
       SQL__PROCEDURE__SOURCE__RECORD

   FROM :inTab as I
   LEFT OUTER JOIN "/BIC/PZTS1CNTRY" AS P
   ON I."/BIC/ZTS1CNTRY" = P."/BIC/ZTS1CNTRY" AND
      P.OBJVERS = 'A' AND
      NOT ( P.RECORDMODE = 'R' );

-- Allow Error Handling for HANA Routines is enabled

if :i_error_handling = 'TRUE' then
   errorTab = select
              'FAREAMISSING' as ERROR_TEXT,
              SQL__PROCEDURE__SOURCE__RECORD as SQL__PROCEDURE__SOURCE__RECORD
              FROM :table1
              where "/BIC/ZTS1FAREA" = '';

end if;
   
outTab = select * from :table1
            where NOT ( "/BIC/ZTS1FAREA" = '' );
 
Subscribe our Newsletter

Keep up to date on SAP Analytics Cloud, SAP Data Hub, and Big Data, and do not miss any news, downloads & events.

Author
Expert Team
blank
blank
blank
blank
blank
blank
blank