SQLScript in SAP HANA: Avoiding NULL Values with SQLScript COALESCE

Sebastian

Written By: Sebastian - 17 December, 2020

If you implement transformation routines with SQL Script, you must ensure that the result table does not contain NULL values. Otherwise an error message will appear. This article will show you how to avoid this problem.

All field values written to the result table OUTTAB must contain a defined value. Because the definition of the active data table of the DSO has a Not NULL check.

DSO table settings

Therefore all fields of the result table in the SQL Script routine must be filled accordingly.

Note that especially SQL queries with OUTER JOINS commands can generate NULL values. Since it depends on the data of the merged tables, whether the corresponding columns are filled during the JOIN. Therefore all columns that are filled by an OUTER JOIN should be secured with a COALESCE function.


Increase the performance of your BW with SQLScript

Neuer Call-to-Action


This function returns the first non-NULL expression from a list. Below are some examples for clarification.

COALESCE (inTab.”CUSTOMER”, ' ') AS “CUSTOMER”
COALESCE (inTab.”CUSTOMER”, '00000000') AS “CALDAY”
COALESCE (inTab.”AMOUNT”, 0) AS “AMOUNT”

The defined value must correspond to the corresponding initial value for the respective data type. Here is an overview of the initial values relevant for SAP BW:

The defined value must correspond to the initial value for the respective data type. Below you will find an overview of the initial values relevant for SAP BW:

Data type

Initial value

CHAR

' '

NUMC length n

n zeros

DATS

00000000

TIMS

000000

CLNT

000

LANG

' '

Numerical types, e.g. CURR, QUAN, FLTP, INT4

0

 

The protection of the OUTER JOIN could look as follows:

outtab = SELECT ...
COALESCE( pcompany.comp_code, ' ' ) AS comp_code,
it.record,
it.sql__procedure__source__record
FROM :intab as it
LEFT OUTER JOIN "/BI0/PCOMPANY" AS pcompany
ON it.company = pcompany.company

Alternatively, you can also implement an error handling with a NULL-value check. The erroneous data sets are transferred to the ERRORTAB and written into the ErrorStack.


ERRORTAB = SELECT 'Incorrect data record' AS error_text,
SQL__PROCEDURE__SOURCE__RECORD
FROM :outtab
WHERE company = ' ' --Empty INTAB field
OR comp_code IS NULL --Join failed

Fazit

Our customers can benefit from significant performance improvements thanks to SQLScript. They can now focus on data analysis and achieving results instead of waiting for the data to be processed.

Do you have any questions about SQLScript or need assistance migrating your transformations? Please do not hesitate to contact us and visit our SAP HANA SQLScript page.

Learn more about  SAP HANA SQLScript

 

Further information can be found in the book "SQLScript for SAP HANA" (SAP PRESS, ISBN 978-3-8362-7408-1) by Jörg Brandeis 

Topics: SAP HANA, SAP BW/4HANA, SAP HANA SQL

Share article