3 Replies Latest reply on Aug 21, 2013 10:34 AM by Robert Morton

    Missing fields after a successful SQL query

    Alexandre DAVID

      Hello,

       

      I have a query that should return let's say 10 fields.

      When I launch this query in SQL, I can see my 10 fields. => OK

      When I launch it in Tableau as a custom query, I can see only 8 fields. => ?

       

      Note that I have also tried with the default query to import the whole table, or with select * or even with only those 2 fields. No matter how I try, I cannot get them. Of course, refresh data does not solve the issue.

       

      It appears that in the case I have today, these columns contain a time information under this form : 06:34:47.0000000.

      When transforming the data so it has this form 06:34:47 it is loaded correctly (as a string but then I can force tableau to consider it as a date/time).

       

      I had this problem several times in the past where Tableau seemed to have proceeded successfully (meaning without error message) to the loading of data, but then I realized that some columns were missing. This is the first time I am able to associate this problem to a data type (however I am not sure this is the key to solve this).

       

      Could you please let me know if you have encountered similar issues and how you have solved them ?

       

      Thank you,

       

      Alexandre

        • 1. Re: Missing fields after a successful SQL query
          Matt Lutton

          I would contact Support about this one.  Sorry, I don't have any further experience to share but they should be able to help you figure out why this is occurring.

          • 2. Re: Missing fields after a successful SQL query
            Alexandre DAVID

            Done. I thought this was some sort of known issue as I had it similar problems several times in earlier versions already.

            • 3. Re: Missing fields after a successful SQL query
              Robert Morton

              Hi Alexandre,

               

              Tableau will skip certain data types which it cannot meaningfully support in a visualization. A common example of this is binary data, but other data types may include INTERVAL (related to date/time) and specialized date/time types such as 'datetime2' on SQL Server. Tableau added support for 'datetime2' a while ago, but it relies on having the latest SQL Server provider for OLE DB installed on your machine. This example demonstrates that other components beyond Tableau can influence whether or not Tableau can recognize and work with certain data types.

               

              Contacting support is the best path forward here, since they can work with you to examine your application logs and gather more information about the problem. In the meantime as a workaround, try changing your Custom SQL connection to explicitly CAST those two columns to a data type you expect would be supported. A simple test would be to cast to VARCHAR(1000) or a similar string representation, just to prove that the underlying data is in fact accessible in some form. Then experiment with casting to a more specific type that you require for your analysis, such as a pure TIME type.

               

              I hope this helps,

              Robert