INSERT INTO statement with empty subquery (JSON_TABLE)

[iperez-sofos]

Greetings.

I am working on a project based on SAP CAP and SAP HANA DB, on the SAP BTP (Trial account) platform.

The project was stopped for a certain time and while I lost access to the Trial account where I had it. So, now I have passed all the content (code) of my project to a new Trial account (at least temporarily).

I have noticed some minor changes in the platform SAP BTP, and in particular, now I am having a runtime error with a stored procedure that has the following logic:

 -- insert region(s)
 INSERT INTO FOUNDATION_CLIMATICREGIONS_MASTER (ID, nameID, country_code, name, descr, createdByTxn)
      SELECT SYSUUID, JT.nameID, JT.country, JT.name, JT.descr, :txnID
        FROM JSON_TABLE(:txnData, '
                 COLUMNS
                 (
                     nameID  NVARCHAR(111)  PATH '$.nameID',
                     country NVARCHAR(3)    PATH '$.country_code',
                     name    NVARCHAR(111)  PATH '$.name',
                     descr   NVARCHAR(1111) PATH '$.descr'
                 )
                 ERROR ON ERROR
             ) AS JT;


 -- insert season(s)
 INSERT INTO FOUNDATION_CLIMATICREGIONS_SEASONS (ID, nameID, climRegion_ID, name, descr,
                                                 timespan_start_mm, timespan_start_dd,
                                                 timespan_end_mm, timespan_end_dd,
                                                 createdByTxn)
      SELECT SYSUUID, JT.nameID, CR.ID, JT.name, JT.descr, JT.start_mm, JT.start_dd, JT.end_mm,        
             JT.end_dd, :txnID
        FROM JSON_TABLE(:txnData, '
                 COLUMNS
                 (
                     regionNameID NVARCHAR(111) PATH '$.nameID',
                     NESTED                     PATH '$.seasons'
                     COLUMNS
                     (
                         nameID NVARCHAR(111) PATH '$.nameID',
                         name   NVARCHAR(111) PATH '$.name',
                         descr  NVARCHAR(1111) PATH '$.descr',
                         start_mm INTEGER PATH '$.timespan_start_mm',
                         start_dd INTEGER PATH '$.timespan_start_dd',
                         end_mm INTEGER PATH '$.timespan_end_mm',
                         end_dd INTEGER PATH '$.timespan_end_dd'
                     )
                 )
                 EMPTY ON ERROR
             ) AS JT
  INNER JOIN FOUNDATION_CLIMATICREGIONS_MASTER AS CR
          ON CR.nameID = JT.regionNameID;

txnData is a parameter that contains data in JSON format that I process and insert into my tables.

The error occurs in the second INSERT INTO statement when the nested path '$ .seasons' is empty ($.seasons is optional).

The error details an insert of NULL values of some columns corresponding to the nested block '$ .seasons'.

Previously this did not happen. When '$ .seasons' was empty it just didn't insert anything into the second table (which is just what I want).

Do you have an idea of how to approach this topic or what to do about it?

Besides, does anyone know if the error may be due to something not supported by HANA in its current version? For example, recently, due to an error that arose in my code, I came across a post on this forum where someone indicated that the UPDATE FROM statement was no longer supported (you have to use MERGE INTO).

Thankful in advance for any help and/or suggestions.