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.