Details

    • Type: Story
    • Status: Done
    • Resolution: Done
    • Affects Version/s: None
    • Fix Version/s: Search Services 2.0
    • Labels:
      None
    • 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

      Description

      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

        Attachments

          Issue Links

            Structure

              Activity

                People

                • Assignee:
                  Unassigned
                  Reporter:
                  klalia Keerat Lalia
                • Votes:
                  0 Vote for this issue
                  Watchers:
                  4 Start watching this issue

                  Dates

                  • Created:
                    Updated:
                    Resolved:

                    Structure Helper Panel