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

Field types and data type conversion issue with Date fields

    Details

      Description

      Currently, Insight Engine returns the field types as String for all the fields, irrespective of the real field type in the Data model.
      This is paeticularly an issue for the date fields with tools like Tableau.
      Tableau allows defining measures and grouping based on certain fields. To group based on Date fields, these fields can be selected and date type can be changed from say string to Date.

      On attempting change of field type for a field say, finance_createdAt (date time field shows up as string), the following error is seen.

      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] 07210051 Unable to execute the query, error caused by: class java.lang.String cannot be cast to class java.lang.Number (java.lang.String and java.lang.Number are in module java.base of loader 'bootstrap'). 
      SELECT CAST(`Custom_SQL_Query`.`finance_CreatedAt` AS DATE) AS `finance_CreatedAt`,
        SUM(CAST(`Custom_SQL_Query`.`finance_amount` AS FLOAT)) AS `sum_finance_amount_ok`
      FROM (
        select finance_CreatedAt, finance_amount  from alfresco 
        where finance_amount > 0
      ) `Custom_SQL_Query`
      GROUP BY 1
      
      

      1. Right field types should be returned by Insight Engine
      2. Converting to date field should be possible.

        Attachments

          Issue Links

            Structure

              Activity

                People

                • Assignee:
                  Unassigned
                  Reporter:
                  mbhave Meenal Bhave [X] (Inactive)
                • Votes:
                  0 Vote for this issue
                  Watchers:
                  1 Start watching this issue

                  Dates

                  • Created:
                    Updated:
                    Resolved:

                    Structure Helper Panel