1 Reply Latest reply on Dec 11, 2012 12:11 PM by Robert Morton

    Custom SQL in DB2 Involves sysdummy1

    suzie.lu.0

      Hi ,

       

      I have a data source on DB2, to meet all reporting needs my custom SQL has a sub query selecting multiple pseudo-columns from sysdummy1 table (a substitute of Dual table as in Oracle). Every time I tried to extract data, my Tableau WB stopped working and popped fatal error. I copied the same query and ran it directly in database, everything worked fine.

       

      Anyone has the similar experience or any thought?

       

      Thanks,

      Suzie

        • 1. Re: Custom SQL in DB2 Involves sysdummy1
          Robert Morton

          Hi Suzie,

           

          Tableau must wrap all queries with Custom SQL connections inside a subquery, which may cause syntax problems when using special system tables and views. In this case it may be that the virtual sysdummy1 table can only be used in an outer query. Tableau uses subqueries for Custom SQL in order to isolate clauses such as WHERE, GROUP BY, etc., so that additional clauses can be added for the analytical queries powering your Tableau visualizations. If you look in your Tableau log files you may be able to see the full query that Tableau is issuing for the Custom SQL connection. Does that query also run fine when run directly in the database?

           

          If the problem in fact is due to subqueries, you will likely not be able to work around this problem and you'll need to explore an alternative to Custom SQL or to your use of the virtual system tables.

           

          I hope this helps,

          Robert