Uploaded image for project: 'Search and Discovery'
  1. Search and Discovery
  2. SEARCH-2354

Support for DAYOFMONTH, DAYOFWEEK, DAYOFYEAR

    Details

    • Type: Story
    • Status: Done
    • Resolution: Done
    • Affects Version/s: None
    • Fix Version/s: Search Services 2.0
    • Labels:
      None

      Description

      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

        Attachments

          Issue Links

            Structure

              Activity

                People

                • Assignee:
                  Unassigned
                  Reporter:
                  abenedetti Alessandro Benedetti
                • Votes:
                  0 Vote for this issue
                  Watchers:
                  3 Start watching this issue

                  Dates

                  • Created:
                    Updated:
                    Resolved:

                    Structure Helper Panel