2 Replies Latest reply on Jan 11, 2019 12:10 PM by David Owens

    [MySQL][ODBC 5.3(w) Driver][mysqld-5.5.5-10.2.12 2.2.11-maxscale]You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'VARCHAR(100)) AS `SoD_build_type`

    David Owens

      Trying to use this query in Tableau but doesn't look like Tableau likes VARCHAR(100) fields.  Gives me a sql error but works fine in MySQL Workbench.
      [MySQL][ODBC 5.3(w) Driver][mysqld-5.5.5-10.2.12 2.2.11-maxscale]You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'VARCHAR(100)) AS `SoD_build_type`

       

      MariaDB database
      Query:

      select

          infs.hostname AS SoD_Hostname,

          ag.name as SoD_Group_Name,

          jj.end_date as SoD_Provsion_Job_Date_Time,

          max(case when icf.name = "server_location" then ocfv.str_value end) as SoD_server_location,

          max(case when icf.name = "server_location_short_name" then ocfv.str_value end) as SoD_server_location_short_name,

          max(case when icf.name = "build_type" then ocfv.str_value end) as SoD_build_type,

          max(case when icf.name = "server_zone" then ocfv.str_value end) as SoD_server_zone,

          max(case when icf.name = "cmdb_lifecycle" then ocfv.str_value end) as SoD_cmdb_lifecycle

      from

          infrastructure_server infs

      join infrastructure_server_jobs isj ON infs.id = isj.server_id

      join jobs_job jj on isj.job_id = jj.id

      join accounts_group ag on infs.group_id = ag.id

      join orders_orderitem ooi on jj.job_parameters_id = ooi.jobparameters_ptr_id

      join orders_provisionserverorderitem_custom_field_values opsoicfv on ooi.jobparameters_ptr_id = opsoicfv.provisionserverorderitem_id

      join orders_customfieldvalue ocfv on opsoicfv.customfieldvalue_id = ocfv.id

      join infrastructure_customfield icf on ocfv.field_id = icf.id

      where

          ag.id IN (2,10,17)

      group by infs.id