Skip to Content
Technical Articles

SAP Advanced SQL Migration. SAP Hana NULL string concatenation

When you convert from one SQL dialect to another you will find many small particular things with a different behavior that you will have to consider, the NULL string concatenation is one of them. The ANSI standard stays that the result of a string concatenation with NULL returns NULL, but some of the current SQL dialects treat (by default) NULL as an empty string when evaluated in a concatenation operation, notice this example in Oracle:

SQL> create table employee (employee_id int,
name varchar (100),
city varchar (50),
zipcode  varchar (10) null);

Table created.

SQL> insert into employee values (1, ‘Javier Martin’, ‘Madrid’, null);

1 row created.

SQL> select name, city || ‘:’ ||  zipcode from employee;

NAME                                                CITY||’:’||ZIPCODE
——————————————–     ——————-
Javier Martin                                      Madrid:

SQL>

   In SQL Server you can modify this behavior with a session parameter called:

CONCAT_NULL_YIELDS_NULL

   In SAP Hana when you concatenate a string with NULL the returned value is NULL (behavior defined by ANSI standard) so queries coming from Oracle doing run-time concatenations may return different values if any term ever happens to be NULL, that is SQL queries may exhibit different semantics when executed in Hana when string concatenation is involved.

The remedy SAP Advanced SQL Migration too has to avoid this undesired behavior is to use the IFNULL Hana Built-in function in the converted code, without it the behaviour is different compared to Oracle:

hdbsql SP4=> select name, city || ‘:’ ||  zipcode from employee;
> go
NAME,CITY||’:’||ZIPCODE
Javier Martin,?
“”
1 row selected (overall time 114,776 msec; server time 3207 usec)

hdbsql SP4=> select name, city || ‘:’ ||  IFNULL (zipcode,”) from employee;
> go
NAME,CITY||’:’||IFNULL(ZIPCODE,”)
Javier Martin,Madrid:
“”””
1 row selected (overall time 75,223 msec; server time 2801 usec)

 

Other related posts:

Be the first to leave a comment
You must be Logged on to comment or reply to a post.