2 Replies Latest reply on Oct 13, 2016 10:44 AM by Mike Loveless

    Custom Sql Query Error when adding a date filter

    Mike Loveless

      Hi all,

       

      I have a custom query in Tableau that is running fine until I try to filter by Relative Date Range, specifically the next 3 months.  The error I'm getting is "An error occured while communicating with data source 'redshift."  When looking at query, it now looks like :

       

      SELECT CAST(EXTRACT(MONTH FROM CAST("custom sql query"."closedate" AS TIMESTAMP WITHOUT TIME ZONE)) AS INTEGER) AS "mn_closedate_ok",

        CAST(EXTRACT(YEAR FROM CAST("custom sql query"."closedate" AS TIMESTAMP WITHOUT TIME ZONE)) AS INTEGER) AS "yr_closedate_ok"

      FROM (

       

      MY ORIGINAL CUSTOM QUERY, SOME CTEs REQUIRED TO GET THE DATA THAT I NEED

       

      ) "custom sql query"

      WHERE (("custom sql query"."closedate" >= (TIMESTAMP '2016-10-01 00:00:00.000')) AND ("custom sql query"."closedate" < (TIMESTAMP '2017-01-01 00:00:00.000')))

      GROUP BY 1,

        2

       

      Within my original query I am casting closedate as a date, originally it is a String that I had to clean up a little to allow for me to cast as date, specifically remove values like NA that were entered.   Everything is now either null or a date value. 

       

      I am able to do everything that I want until the filter is added.  Tableau is aggregating different values with multiple rows as expected, which I have removed for now to try and limit the modified Tableau query to only the relevant information.  The error has only appeared when I'm adding the filter.  Any ideas?

       

      Thanks

        • 1. Re: Custom Sql Query Error when adding a date filter
          Mike Loveless

          To add a little more that may be helpful, I'm getting the same error if I add 2 measures.  For a short term work around I was using the year and month that I have in my columns as a filter (don't really like this as next month it will include Nov 2016 - Jan 2017 as well as Jan 2016).  This works fine with 1 measure, but when I add a second it give me the same error.  It will show either of the two measures fine if they are the only one, it doesn't matter which one I include first.  SQL would now look like:

           

          SELECT CAST(EXTRACT(MONTH FROM CAST("custom sql query"."closedate" AS TIMESTAMP WITHOUT TIME ZONE)) AS INTEGER) AS "mn_closedate_ok",

            SUM("custom sql query"."discountedmontly") AS "sum_discountedmontly_ok",

            SUM("custom sql query"."estimatedmontly") AS "sum_estimatedmontly_ok",

            CAST(EXTRACT(YEAR FROM CAST("custom sql query"."closedate" AS TIMESTAMP WITHOUT TIME ZONE)) AS INTEGER) AS "yr_closedate_ok"

          FROM (

           

           

          MY ORIGINAL CUSTOM QUERY

           

           

          ) "custom sql query"

          WHERE ((CAST(EXTRACT(MONTH FROM CAST("custom sql query"."closedate" AS TIMESTAMP WITHOUT TIME ZONE)) AS INTEGER) IN (10, 11, 12)) AND (CAST(EXTRACT(YEAR FROM CAST("custom sql query"."closedate" AS TIMESTAMP WITHOUT TIME ZONE)) AS INTEGER) = 2016))

          GROUP BY 1,

            4

          • 2. Re: Custom Sql Query Error when adding a date filter
            Mike Loveless

            Wanted to follow up on this if anyone ever comes across this from searching.  Tableau was evaluating some non date fields that I had filtered out in a WHERE statement and trying to convert those into a date which was causing the error.  As a work around, I created my own isdate flag (Redshift doesn't use isdate) and used a case statement based on the flag to cast to date.  Something about the way Tableau evaluates the filters created in Tableau disregarded the filters that I had already applied.