-
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 41
-
Release Train:Southall
-
Delivery Team:Search
-
Story Points:8
When trying to create a line graph (continuous) for this data it produces the following error in tableau:
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] 05220197 Unable to execute the query, error caused by: Cannot apply 'EXTRACT' to arguments of type 'EXTRACT(<INTERVAL DOY> 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(1) AS `sum_Number_of_Records_ok`, {fn TIMESTAMPADD(SQL_TSI_DAY,{fn FLOOR((-1 * ( {fn DAYOFYEAR(`Custom_SQL_Query1`.`cm_created`)} - 1)))},CAST(`Custom_SQL_Query1`.`cm_created` AS DATE))} AS `tyr_cm_created_ok` FROM ( select *, expense_Amount from alfresco ) `Custom_SQL_Query1` GROUP BY `Custom_SQL_Query1`.`TYPE`, \{fn TIMESTAMPADD(SQL_TSI_DAY,{fn FLOOR((-1 * ({fn DAYOFYEAR(`Custom_SQL_Query1`.`cm_created`)} - 1)))},CAST(`Custom_SQL_Query1`.`cm_created` AS DATE))}
Because of that the following functions must be tested/implemented:
https://www.w3schools.com/sql/func_mysql_dayofyear.asp
https://www.w3schools.com/sql/func_mysql_dayofmonth.asp
https://www.w3schools.com/sql/func_mysql_dayofweek.asp
Acceptance criteria:
- the described functions as SQL standard documentation linked
- use a separate field if necessary
- causes
-
SEARCH-2316 Error is produced in Tableau when trying to produce a graph for number of nodes created by year and type
- Review