Changelog query converts CURRENT_DATE HANA Variable to CURRENT_DATE() which give an invalid query.

[llouw-drs]

I have the following case statement on a table for the ON READ. For some reason the query gets converted to current_date() which does not exist in HANA. I am unsure if it is the changelog doing it or the CAP cds-dbs module, but this only happens on change logs getting saved.

case 
when (ReportHeaders.status_code = ? and ReportHeaders.dueDate < ReportHeaders.modifiedAt) then true 
when (ReportHeaders.status_code <> ? and ReportHeaders.dueDate < current_date)

Query generated by cap-js/changelog

WITH ReportHeaders as (SELECT *,('$[' || lpad("$$RN$$",6,'0')) as _path_ FROM (SELECT *,ROW_NUMBER() OVER () as "$$RN$$" FROM (SELECT ReportHeaders.ID,ReportHeaders.createdAt,ReportHeaders.createdBy,ReportHeaders.modifiedAt,ReportHeaders.modifiedBy,ReportHeaders.createdByName,ReportHeaders.modifiedByName,ReportHeaders.report_ID,ReportHeaders.payrollYear,ReportHeaders.payrollPeriod,ReportHeaders.payrollArea,ReportHeaders.position_ID,ReportHeaders.organisation_ID,ReportHeaders.payPeriodStartDate,ReportHeaders.payPeriodEndDate,ReportHeaders.previousPayrollYear,ReportHeaders.previousPayrollPeriod,ReportHeaders.status_code,ReportHeaders.assigned_ID,ReportHeaders.processId,ReportHeaders.escalationId,ReportHeaders.dueDate,case when (ReportHeaders.status_code = ? and ReportHeaders.dueDate < ReportHeaders.modifiedAt) then true when (ReportHeaders.status_code <> ? and ReportHeaders.dueDate < current_date()) then true else false end as isLate,case when (ReportHeaders.status_code = ? and ReportHeaders.dueDate < ReportHeaders.modifiedAt) then days_between(ReportHeaders.dueDate,ReportHeaders.modifiedAt) when (ReportHeaders.status_code <> ? and ReportHeaders.dueDate < current_date()) then days_between(ReportHeaders.dueDate,current_date()) else ? end as daysLate FROM tmr_csr_db_ReportHeaders as ReportHeaders WHERE ReportHeaders.ID = ?) as ReportHeaders) as ReportHeaders) SELECT "_path_","_blobs_","_expands_","_json_" FROM (SELECT _path_ as "_path_",'{}' as "_blobs_",'{}' as "_expands_",(SELECT ID as "ID",to_char(createdAt, 'YYYY-MM-DD"T"HH24:MI:SS.FF3"Z"') as "createdAt",createdBy as "createdBy",to_char(modifiedAt, 'YYYY-MM-DD"T"HH24:MI:SS.FF3"Z"') as "modifiedAt",modifiedBy as "modifiedBy",createdByName as "createdByName",modifiedByName as "modifiedByName",report_ID as "report_ID",payrollYear as "payrollYear",payrollPeriod as "payrollPeriod",payrollArea as "payrollArea",position_ID as "position_ID",organisation_ID as "organisation_ID",to_char(payPeriodStartDate, 'YYYY-MM-DD') as "payPeriodStartDate",to_char(payPeriodEndDate, 'YYYY-MM-DD') as "payPeriodEndDate",previousPayrollYear as "previousPayrollYear",previousPayrollPeriod as "previousPayrollPeriod",status_code as "status_code",assigned_ID as "assigned_ID",processId as "processId",escalationId as "escalationId",to_char(dueDate, 'YYYY-MM-DD') as "dueDate",isLate as "isLate",daysLate as "daysLate" FROM JSON_TABLE('[{}]', '$' COLUMNS("'$$FaKeDuMmYCoLuMn$$'" FOR ORDINALITY)) FOR JSON ('format'='no', 'omitnull'='no', 'arraywrap'='no') RETURNS NVARCHAR(2147483647)) as "_json_" FROM ReportHeaders) ORDER BY "_path_" ASC

[Sv7enNowitzki]

Hi @llouw-drs ,

This looks a bit strange. Could you provide an app for me to reproduce it so that I can debug it?

Best Regards,
Wenjun

[Sv7enNowitzki]

Hello @llouw-drs ,

I guess you might be using @cap/js-hana. As far as I understand, they fixed the issue with the case sensitivity of current_date in the latest version, which is 1.5.0. I'm not sure if that's the problem you encountered before, because from the perspective of change tracking, there's never any operation to hijack similar behavior. So if possible, please upgrade to the latest version and give it another try.

Best Regards,
Wenjun

[llouw-drs]

Hi @Sv7enNowitzki,

I am indeed using @cap-js/hana, but I am not so sure case sensitivity is the problem. It is more the fact that it adds the () at the end of current_date. I actually resorted in calling session_context('$now') as a workaround for now.

Win I get a bit of time I will test it with upgrade packages and see what it does.

Thanks,
Lochner

[Sv7enNowitzki]

Hi @llouw-drs ,

Updating to the latest version for a try is one approach. In the meantime, while trying the new version, could you kindly provide some more specific information? Due to differences in environments, there might be many erroneous analysis results.

Best Regards,
Wenjun