About COALESCE, Left outer join, NULL and the link between them

Int4 Team

2020-12-13

In this article you will learn about:

– What you should bear in mind while joining tables in AMDP

– Usage of COALESCE function

Reading time: 5 minutes

Introduction

ABAP Database Managed Procedures (AMDP) – I guess everyone has already heard about it. It’s a powerful feature allowing ABAP developers to write database procedures utilizing code to data push-down approach. AMDP can significantly reduce the runtime of an ABAP program. There are also many other benefits but that’s not what this article is about. Today, I would like to pay attention to one possible issue as a result of left outer joins.

Recently, I encountered an issue in one of my AMDPs. I discovered that I’m missing some data in the result table. However, my missing data was visible in each of the joined tables. I double checked and confirmed that the data defined in the LEFT OUTER JOIN conditions was the same. However, the join didn’t fetch the data from the second table. I started to wonder, what caused the issue?

Left outer join – where’s the catch?

LEFT OUTER JOIN, by definition, selects the complete set of records from the first (left) table, with the matching records in the second (right) table.

Let us have a look at the following example:

Left table:

Right table:

Our goal is to fetch delivery data (left table) with information about batches (right table). We will join these tables on the following columns: MANDT, MATNR, CHARG, WERKS, LGORT. In this case, the left outer join statement in AMDP may look like this:

lt_deliveries  = SELECT * FROM zdoc_table1;
lt_batches     = SELECT * FROM zdoc_table2;

lt_del_batches = SELECT t1.mandt, t1.vbeln, t1.posnr,
                        t1.matnr, t1.charg, t1.werks,
                        t1.lgort, t1.lfimg, t1.meins,
                        t1.vrkme,
                        t2.ersda, t2.ernam, t2.lfgja,
                        t2.clabs
                   FROM :lt_deliveries AS t1
                   LEFT OUTER JOIN :lt_batches AS t2
                   ON  t1.mandt = t2.mandt
                   AND t1.matnr = t2.matnr
                   AND t1.lgort = t2.lgort
                   AND t1.werks = t2.werks
                   AND t1.charg = t2.charg;

Result of the join (yellow fields were fetched from the right table):

We got the correct results. Please note that HANA properly joined a row where one key column in both tables was empty (CHARG column – the last but one row).

Now, let’s focus on the problematic scenario.

In my case, I used similar left outer join conditions as in the example above (joined columns are marked in green).

Left table – LT_WITH_BILLING:

Right table – LT_ZDOC_DATA:

Here is the join statement and the result table:

      lt_items = SELECT DISTINCT f.rbukrs,  f.gjahr, f.belnr,
                                f.docln, f.matnr, f.werks, f.prctr,
                                f.ebeln, f.ebelp, f.aubel,
                                l.txt, l.status
                          FROM :lt_with_billing AS f
                          LEFT OUTER JOIN :lt_zdoc_data AS l
                               ON f.rclnt = l.mandt
                              AND f.rbukrs = l.rbukrs
                              AND f.aubel = l.aubel
                              AND f.gjahr = l.gjahr
                              AND f.belnr = l.belnr
                              AND f.docln = l.docln;

Please notice that we’re missing data from the right table in the last two rows. How is that possible if the values exist in the LT_ZDOC_DATA table? The only possible answer is that values in some columns defined in the join conditions are not the same. Our first suspicion should be the AUBEL column. We already proved that it is possible to define joins over empty columns. Therefore, we may assume that at least one column (in either left or right tables ) has a null value. Null is not equal to empty – an empty string is a string instance of zero length, whereas a null string has no value at all. We can easily check our hunch by using IFNULL operator:

lt_items = SELECT DISTINCT f.rbukrs,  f.gjahr, f.belnr,
                                f.docln, f.matnr, f.werks, f.prctr,
                                f.ebeln, f.ebelp, f.aubel,
                                IFNULL( f.aubel, 'YES' ),
                                l.txt, l.status
                          FROM :lt_with_billing AS f
                          LEFT OUTER JOIN :lt_zdoc_data AS l
                               ON f.rclnt = l.mandt
                              AND f.rbukrs = l.rbukrs
                              AND f.aubel = l.aubel
                              AND f.gjahr = l.gjahr
                              AND f.belnr = l.belnr
                              AND f.docln = l.docln;

Query result:

We guessed correctly! It is clear that null and empty strings are not the same and that’s the reason why we didn’t get the results from the LEFT OUTER JOIN. Let’s go back to the coding and check how the null was populated in our table:

lt_with_billing = SELECT d.*, f.vbeln AS vbeln_b, f.posnn AS posnr_b,
                               f.erdat_b, f.erzet_b, f.fkdat_b,
                               p.aubel, p.aupos
                          FROM :lt_with_delivery AS d
                          LEFT OUTER JOIN :lt_vbfa AS f
                          ON f.mandt = d.rclnt
                          AND f.vbelv = d.vbeln_im
                          AND f.posnv = d.posnr_im
                          LEFT OUTER JOIN vbrp AS p
                          ON p.mandt = f.mandt
                          AND p.vbeln = f.vbeln
                          AND p.posnr = f.posnn;

The null value in AUBEL column for document 5000000010 was populated by the second join with VBRP table as there was no matching billing document.

Solution

How can we solve this problem? The aim is to join two tables where one column which is used as a join condition is empty and another column has a null value. It turns out that solution is fast and simple: COALESCE function.

A COALESCE function returns the first non-NULL expression from a specified list. Usually, we use COALESCE as one of the elements in the select list, however, it can be successfully used in the join conditions too.

Code with the COALESCE function:

 lt_items = SELECT DISTINCT f.rbukrs,  f.gjahr, f.belnr,
                                f.docln, f.matnr, f.werks, f.prctr,
                                f.ebeln, f.ebelp, f.aubel,
                                l.txt, l.status
                          FROM :lt_with_billing AS f
                          LEFT OUTER JOIN :lt_zdoc_data AS l
                               ON f.rclnt = l.mandt
                              AND f.rbukrs = l.rbukrs
*                             AND f.aubel = l.aubel
                              AND COALESCE(f.aubel,'') = COALESCE(l.aubel,'')
                              AND f.gjahr = l.gjahr
                              AND f.belnr = l.belnr
                              AND f.docln = l.docln
                          ORDER BY rbukrs, gjahr, belnr, docln;

As you can notice, instead of f.aubel = l.aubel condition we used COALESCE(f.aubel,’ ‘) = COALESCE(l.aubel,’ ‘). In this case, when the f.aubel or l.aubel column is null, COALESCE will return a second value from the arguments list which is an empty string and HANA should properly join these two columns.

Result:

Summary

AMDP is a very powerful and useful development approach and as shown in this blog, we always have to be careful to get correct results. Even though in a debugger we can see that there is no value in the column, it can turn out that a null is hidden there and it can affect our final expected results. Fortunately this issue can be easily resolved with the COALESCE function used in the JOIN condition.

Personally I would like the Eclipse team to improve the table preview and mark null columns as a <null> value instead of displaying them as if they were empty strings. That would make the developer’s life a bit easier.