Hi,
I want to create a Selection View over a HIVE data source and encountered an error within the WHERE condition that I fail to solve.
Users should be able to enter a date range using the fields "from_timestamp" and "to_timestamp" of type "timestamp". These inputs have to be converted into strings since the source has partitioning over year-month. Because of this, we need to convert timestamps to a text: 'yyyy-MM'.
Field in the baseview:
| Name | Type | Example |
| -------- | -------- | -------- |
| month | text | '2023-01' |
Following documentation, I wanted to use formatdate() to convert the timestamp to a string, and then use substr() to cut out the "yyyy-MM" part.
This works fine if i test it in VQL Shell:
`select substr(formatdate('yyyy-MM-dd HH:mm:ss', TIMESTAMP '2023-01-20 10:16:45.673'), 1, 7)`
But it some how does not, when applied in the view itself:
Where condition in Denodo:
`viewname.month >= substr(formatdate('yyyy-MM-dd HH:mm:ss', from_timestamp), 1, 7)`
Query pushed to source:
`WHERE t0.month >= SUBSTR(
date_format(
from_utc_timestamp(
unix_timestamp('2023-01-10 10:16:45.000', 'yyyy-MM-dd HH:mm:ss'),
'yyyy-MM-dd HH:mm:ss')
),
1,7)`
Because of this query HIVE complains:
> SemanticException [Error 10015]: Line 1:3390 Arguments length mismatch ''yyyy-MM-dd HH:mm:ss'': date_format requires 2 argument(s), got 1),
Why does the query pushed to HIVE only contain 1 argument for date_format() instead of 2?
Is there setting that prevents Denodo to push down type conversions within the WHERE condition? Since the conversion works in VQL Shell just pushing the result of
` substr(formatdate('yyyy-MM-dd HH:mm:ss', from_timestamp), 1, 7) ` should work.
