Published at September 3, 2020by Jörg Brandeis

Until now, I had never given any thought to the compatibility between SAP HANA Cloud and HANA 2.0. The backward compatibility of ABAP is legendary; you could always rely on SAP in that regard. However, while running the installation scripts for the demo data model of my book, an attentive reader noticed that this produces an error in the SQL console of the SAP HANA Cloud Database Explorer:

So I set out to find information about the compatibility between SAP HANA 2.0 and SAP HANA Cloud systems with regard to SQLScript. The central reference on this topic is 2868742 - Differences Between SAP HANA Cloud and SAP HANA Platform for SQL, SQLScript, and SAP HDI (SAP HANA Deployment Infrastructure). The reason for the incompatibilities is supposedly the memory optimization of the cloud version of SAP HANA, which is why some unimportant features were removed. No specific differences are named in the note, but the corresponding sections in the SAP documentation are linked. For the area of SQL and SQLScript, the page SQL and SQLScript Compatibility of the document SAP HANA Cloud Compatibility Reference is linked.

This lists all features that are no longer supported. The list is frighteningly long, but most of it is rather unimportant for application developers. Primarily, DDL and DCL statements are named. The set of language features that are dropped in the areas of DML and SQLScript in general is relatively manageable. I have created a summary here:

• CE functions may no longer be used. This is overdue, after John Appleby already wrote a worthwhile obituary about them back in 2014: Calculation Engine (CE) Functions – Rest in Peace
• CREATE <user_defined_function_type> FUNCTION - This must be some hidden syntax, because this variant cannot be found in the reference documentation.
• CREATE PROCEDURE … LANGUAGE RLANG - OK, no more R functions
• CREATE PROCEDURE … WITH RESULT VIEW - Table functions can be used instead
• CALL <Procedure> … WITH VERSION VALIDATION
• CALL <Procedure> … WITH OVERVIEW
• SQL function LANGUAGE(<Text>)
• The data type VARCHAR is now only used as an alias for NVARCHAR! The same applies to the data types CLOB and CHAR.
• The data type ALPHANUM is dropped. Likewise the SQL function TO_ALPHANUM.
• SELECT … FROM <Hierarchy Column View>
• SELECT … WHERE CONTAINS … LINGUISTIC
• SELECT … WHERE CONTAINS … FULLTEXT( { ON | OFF | AUTOMATIC })
• SELECT … GROUP BY { GROUPING SETS | ROLLUP | CUBE } TEXT_FILTER <Filter> FILL UP SORT MATCHES TO TOP.
• SELECT … AS OF { COMMIT ID | UTCTIMESTAMP }
• SELECT … WITH RANGE_RESTRICTION
• SELECT … WITH HINT ( ) use in subqueries
• SELECT … WITH DATAPROVISIONING PARAMETERS

However, in these documents I found no indication of why the UPDATE FROM statement is not supported. SAP Note 2241598 - Changed UPDATE FROM syntax can cause ‘invalid table name’ error could not help either, although it concerns the subject area. But maybe it is not due to the compatibility of SAP HANA Cloud, but rather to the Database Explorer. Here is the code for it, maybe someone has a suggestion:

	UPDATE tasks
       SET create_date = datum
      FROM :lt_dates AS dates,
		    tasks
      WHERE tasks.id = dates.id;

The installation script was of course updated immediately, so that it can now be run in every HANA SQL console known to me.

And I found one more thing: instead of the DB user SYSTEM, the DBADMIN should now be used.

Addendum from November 2020

I have migrated my SQLScript e-learning courses to SAP HANA Cloud. In doing so, the participants noticed that the SQL function TO_ALPHANUM no longer works. This is because the data type ALPHANUM has been abolished. As an alternative, the SQL function ABAP_ALPHANUM can be used, which has been available in HANA 2.0 since SPS01.