Tableau Custom Query Error

Question

I'm getting the following error when trying to run the below custom SQL query against Amazon Redshift into Tableau desktop. I am able to "Preview" in Tableau but it does not work when I actually try to use this as a datasource.

[Amazon][RedShift ODBC] (30) Error occurred while trying to execute a query: ERROR: Invalid digit, Value 'L', Pos 0, Type: Double DETAIL: ----------------------------------------------- error: Invalid digit, Value 'L', Pos 0, Type: Double code: 1207 context: LNDL527501 query: 1611757 location: :0 process: query0_71 [pid=0] --------------------

 SELECT d.dat, m.merch_id, q1.miles_purchased, q1.total_units, 
            CASE
                WHEN date_diff('day'::text, m.first_trans::timestamp without time zone, d.dat::timestamp without time zone) < 0 THEN 0::bigint
                ELSE date_diff('day'::text, m.first_trans::timestamp without time zone, d.dat::timestamp without time zone)
            END AS days_open
       FROM schemaname.all_dates d
      CROSS JOIN ( SELECT t.merch_id, min(t.transaction_date)::date AS first_trans
               FROM schemaname.transactions t
              GROUP BY t.merch_id) m
       LEFT JOIN ( SELECT t.merch_id, t.transaction_date::date AS trans_date, sum(t.settlement_amt) / 16.5 * 60::numeric AS miles_purchased, sum(t.settlement_amt) / 16.5 AS total_units
          FROM schemaname.transactions t
         GROUP BY t.merch_id, t.transaction_date::date) q1 ON m.merch_id::text = q1.merch_id::text AND d.dat = q1.trans_date;

 - `List item`

Alex Blakemore Alex Blakemore · Accepted Answer · 2018-01-27T18:02:48

You are making life hard on yourself (and inefficient) by hard coding your solution in custom SQL and then using Tableau simply to chart the results of your specific hardcoded SQL.

If you are using Tableau, then let Tableau generate the SQL - that approach is much more flexible and efficient. Avoid custom SQL in Tableau - it's an option only needed for rare situations nowadays - and it prevents Tableau from performing the kinds of query optimizations that can make Tableau fast.

To do that, you have to trust the tool and learn to work with the Tableau concepts. Its analogous to using a high level language and trusting that the compiler generates accurate efficient machine code. You get a similar boost in productivity and portability by learning to use Tableau features and letting it generate the queries. Not to say that understanding database concepts and SQL won't be helpful.

So, instead of custom SQL just connect directly to then transactions table and then learn to use calculated fields, filters, table calcs, LOD calcs and other features to do your analysis. Those features can accomplish most or all of what your query attempts.

Finally, if you insist on custom SQL, try removing the trailing semicolon from the query

Tableau Custom Query Error

1 Answers