NOW() function not working with Apache Drill

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.

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.