Skip to Content
0
Jan 14, 2021 at 05:35 PM

Default value for a date field in the JSON_TABLE(json) function of SAP HANA

127 Views Last edit Jan 14, 2021 at 05:37 PM 2 rev

Greetings.

I think the title introduces a bit what the issue is. I describe it in detail below.

I am using the SAP HANA JSON_TABLE() function to query and extract data from a JSON text.Data that is then inserted into a table.

One of the data fields in the JSON text is a date, which is optional (that is, it might not be in the JSON).If the date does not exist in the JSON text, I must take (insert in the table) the current system date.I want to use an instruction like the following:

INSERT INTO MY_TAB (id, genBatch, stage, breed, gender,product, qty, edate, createTr)
     SELECT JT.id, JT.genBatch, JT.stage, JT.breed, JT.gender, JT.prod, TO_DOUBLE(JT.qty),
            JT.bdate, :txn
       FROM JSON_TABLE(:tData, '
                COLUMNS
                (
                    id NVARCHAR(36) PATH '$.id',
                    genBatch NVARCHAR(36) PATH '$.externalID',
                    breed NVARCHAR(36) PATH '$.breed',
                    gender NVARCHAR(80) PATH '$.gender',
                    stage NVARCHAR(36) PATH '$.stage',
                    prod NVARCHAR(36) PATH '$.product',
                    qty NVARCHAR(50) PATH '$.quatity',
                    bdate DATE PATH '$.date' DEFAULT CURRENT_DATE ON EMPTY
                )
            ) AS JT;

The previous code has a syntax error, which I suppose has to do with the fact that the SAP HANA SQL function CURRENT_DATE is only valid to invoke it in the SELECT and WHERE clauses.

My specific questions are:

Is it possible to do what I am looking for?

What option do I have to do it?

I have tried putting the value of the current date in a local variable and then using the value of that variable in the expression (DEFAULT :lv_currDate ON EMPTY), but it doesn't work.It seems to me that the expression with which the default value can be assigned to an empty field in the JSON_TABLE function must necessarily be a constant expression (although I really haven't found a documentation where they say it explicitly).

Note:I am working with SAP HANA Express Edition SPS4 on my local computer.

Thanks in advance for any answers and / or suggestions.