-
Type:
Story
-
Status: Done
-
Resolution: Done
-
Affects Version/s: None
-
Fix Version/s: Search Services 2.0
-
Labels:None
-
Epic Link:
-
Sprint:Team Ninja-King - S&I 37, Team Ninja-King - S&I 38, Team Ninja-King - S&I 39, Team Ninja-King - S&I 40
-
Release Train:Southall
-
Delivery Team:Search
-
Story Points:20
When trying to execute the following query on tableau: Total number of modifications per month within 60 day period, the filter to get the results within the 60 day period produces an error.
The following error is returned:
An error occurred while communicating with the Other Databases (ODBC) data source 'Custom_SQL_Query (CData)'. Bad Connection: Tableau could not connect to the data source. [400] 05220226 Unable to execute the query, error caused by: Cannot apply 'EXTRACT' to arguments of type 'EXTRACT(<INTERVAL YEAR> FROM <JAVATYPE(CLASS JAVA.LANG.STRING)>)'. Supported form(s): 'EXTRACT(<DATETIME_INTERVAL> FROM <DATETIME_INTERVAL>)' 'EXTRACT(<DATETIME_INTERVAL> FROM <DATETIME>)'. SELECT `Custom_SQL_Query1`.`TYPE` AS `TYPE`, SUM(`Custom_SQL_Query1`.`cm_content.size`) AS `sum_cm_content_size_ok`, \{fn YEAR(`Custom_SQL_Query1`.`cm_modified`)} AS `yr_cm_modified_ok` FROM ( select *, expense_Amount from alfresco ) `Custom_SQL_Query1` INNER JOIN ( SELECT \{fn YEAR(`Custom_SQL_Query1`.`cm_modified`)} AS `yr_cm_modified_ok` FROM ( select *, expense_Amount from alfresco ) `Custom_SQL_Query1` GROUP BY \{fn YEAR(`Custom_SQL_Query1`.`cm_modified`)} HAVING (MIN(`Custom_SQL_Query1`.`cm_created`) > \{ts '2020-04-22 00:00:00'}) ) `t0` ON ((\{fn YEAR(`Custom_SQL_Query1`.`cm_modified`)} = `t0`.`yr_cm_modified_ok`) OR ((\{fn YEAR(`Custom_SQL_Query1`.`cm_modified`)} IS NULL) AND (`t0`.`yr_cm_modified_ok` IS NULL))) GROUP BY `Custom_SQL_Query1`.`TYPE`, \{fn YEAR(`Custom_SQL_Query1`.`cm_modified`)}
I have tried other ways to filter the results and get the same issue. I have tried creating data parameters and creating calculation to keep the data within a certain time frame. I have applied a filter to the date field itself and setting a condition where it is a minimum of a 60 day period (2 months).
Acceptance Criteria:
- Investigate if we can apply the filter for this query in a different way
- Fix this issue when trying to apply the filter to the field and implement the fix if possible
- Test the query through tableau and ensure it is working as expected and we can filter a date field
- relates to
-
SEARCH-1995 High-level Insight Engine Acceptance Tests
- Done
-
SEARCH-2316 Error is produced in Tableau when trying to produce a graph for number of nodes created by year and type
- Review