1 Reply Latest reply on Jun 21, 2016 5:05 AM by lenaic.ridinger

    Calculated field without alias in query causes Custom SQL Query fail when updating the result

    Iryna Tyshko

      Hi everyone,

       

      I am working on ODBC driver for a Hive-based system and tested the Custom Query feature with the calculated field that has no alias. While I could execute the query I could not obtain results because the original query was saved in temporary table with auto-generated labels for calculated fields.

      And that table would not be available for subsequent references. 

       

      Original query:

       

      SELECT col_id, {fn TAN(col_id)}

      FROM test_table

       

      Tracing the calls of ODBC functions showed that Tableau generates multiple queries:

       

      # 1

      SELECT *

      FROM (

        select col_id, {fn tan(col_id)} from stream_test_int

      ) Custom_SQL_Query

      LIMIT 1

       

      # 2

      SELECT 1

       

      # 3

      SELECT SUM(1) AS COL

      FROM (

        select col_id, {fn tan(col_id)} from stream_test_int

      ) Custom_SQL_Query

      HAVING COUNT(1)>0

       

      # 4

      SELECT SUM(1) AS COL,

        2 AS COL2

      FROM (

        select col_id, {fn tan(col_id)} from stream_test_int

      ) Custom_SQL_Query

      GROUP BY 2

      HAVING COUNT(1)>0

       

      At this point schema of the query (calculated field gets name “_c0”) becomes available and button Update Now appears

      Tableau_Custom_ERROR.png

       

      Then I press Update Now and trace one more query:

      # 5

      SELECT 1 AS Number_of_Records,

        Custom_SQL_Query._c0 AS XcB,

        Custom_SQL_Query.col_id AS col_id

      FROM (

        select col_id, {fn tan(col_id)} from stream_test_int

      ) Custom_SQL_Query

      LIMIT 1000

       

       

      That one fails with the message indicating that the column name in the query-created table does not exist.

      Screen Shot 2016-03-13 at 5.21.13 PM.png

       

      I do understand that Tableau treats table Custom_SQL_Query as a data source and independent table.

      Adding an alias for TAN(col_id) column fixes the issue. But the query without an alias is valid too.

       

      So I have two questions:

       

      1) Is it possible to get Tableau processing the calculated fields without aliases correctly? Maybe some of SQLGetInfo features affect the query composition workflow?

      2) Why does Tableau generate so many queries, especially SELECT 1 — so far related to the query?

       

      Thank you in advance,

      Iryna