NOW() function not working with Apache Drill

Technical queries
, , , , ,
#1

Hello Helical Insight,

I am using Helical Insight 4.0. I want to use NOW() function in the custom filter value. This function is supported by Apache Drill, but I am getting a SQL error when I am using it in Helical Insight filter value. Please help.

Thank You,
Netta.

#2

Hello Netta,

This issue requires change in the dialect file for Apache Drill.
Edit the file drill.js present at location C:\Program Files\Helical Insight\hi\hi-repository\System\Admin\SqlFunctions
At line 128, add the following logic:

else if(arrayElement == 'NOW()'){
                                arrayElement = arrayElement.replaceAll("'", "").trim();
                                actualFilterValuesArrayWithOutQuotes.pushUnique(arrayElement);
                                actualFilterValuesArray.pushUnique(arrayElement);
                                otherReplaceFilterValuesArray.pushUnique(arrayElement);
						}

image

Refresh the cache and use NOW() function in the custom filter value. The correct SQL will be generated.
Please Note: The logic has been specifically added for this particular function.

Thank You,
Helical Insight.