10 Replies Latest reply on Dec 5, 2016 6:05 AM by Rahul Upadhye

    Custom SQL

    Rahul Upadhye

      Background:

      Im hitting multiple tables in a SQL Server database and the db is well-optimized (i.e. data structure cannot be changed at all).

      All I can expect is, read-only access, nothing more than that.

       

       

      Problem in Tableau:

      Tableau Dashboard is controlled by a Data Parameter displayed as a calendar. I want to pass this parameter into the db to run the dashboard.

      The DB_Date is an integer (ex: 20161202)

      Ideally I want a custom sql query as below:

           select *

           from MS_SQLSERVER_Table1

           where <Parameters.Report Date> = DB_Date

       

      Challenge:

      I cannot pass this Data Parameter as an Integer number in the custom sql to match the DB_Date column. Why its impossible to convert the parameter type to a different data type? Even the sql function "cast as int" did not work.

       

       

      Work around:

      String, Float or even, Integer parameters works fine and it can be passed in the custom sql, but we want a calendar parameter, which can only be achieved either by Date Parameter or Date Time Parameter.

       

      Your input is greatly appreciated...

        • 1. Re: Custom SQL
          Rima Upadhye

          Hi Rahul Upadhye,

           

          You can use convert() function to pull out data from SQL.

          Query:                                                                           

            

          Query LineResult

          CONVERT(VARCHAR(19),GETDATE())

          Nov 04 2014 11:45 PM
          CONVERT(VARCHAR(10),GETDATE(),10)11-04-14
          CONVERT(VARCHAR(10),GETDATE(),110)11-04-2014
          CONVERT(VARCHAR(11),GETDATE(),6)04 Nov 14
          CONVERT(VARCHAR(11),GETDATE(),106)04 Nov 2014
          CONVERT(VARCHAR(24),GETDATE(),113)

          04 Nov 2014 11:45:34:243

           

          In your case since the date is in yyyymmdd format, use below piece of code

           

          SELECT *, convert(date, [GRADUATION_DATE])   FROM  MS_SQLSERVER_Table1

          where CONVERT(date, [<Parameters.Report Date>]) = DB_Date

           

           

          Hope that helps.

          1 of 1 people found this helpful
          • 2. Re: Custom SQL
            Stephen Rizzo

            Do you have a date table in your database? If so, you could simply join your data to the date table on [DB_Date], then use a date-formatted field from the date table for the filter in the WHERE clause. If you don't have one in your database, you could leverage the cross-database join feature to bring one in from another source.

            • 3. Re: Custom SQL
              Rahul Upadhye

              Good thought Rima.

              As per my observations, Tableau's custom sql editor cannot handle complex sql syntax, i tried converting data type string to int, int to date, date to string etc... but that never worked. declaring variables was not supported either.

              Your query gave this error

              • 4. Re: Custom SQL
                Rahul Upadhye

                Thanks Stephen, good suggestion.

                Currently date is not a separate table, rather its a column present in every table.

                I will try this option i feel it should work.

                 

                My only concern being, Joining Tables results in a Cartesian Product, will this create unnecessary rows?

                especially our tables are not indexed on dates and date was never a primary key

                • 5. Re: Custom SQL
                  Łukasz Majewski

                  Try this

                   

                  ...

                  where DB_DATE_INT = YEAR(<Parameters.Date Parameter>)*10000+MONTH(<Parameters.Date Parameter>)*100+DAY(<Parameters.Date Parameter>)

                  1 of 1 people found this helpful
                  • 6. Re: Custom SQL
                    Rahul Upadhye

                    Thanks Lukasz,

                    Exactly same query I tried this afternoon. To my surprise it showed and error too,

                    (i dont have screenshot of that error now)

                    but the error was cannot compare datetime (or date) and int

                     

                    <Parameters.Date Parameter> as a date and as a datetime didnt worked.

                     

                    I even converted <Parameters.Date Parameter> to string and split+ concatenate the string to appear exactly as yyyymmdd and made it int using the "cast as int" syntax.... it did not work either...

                     

                    Raised a ticket at Tableau Support Team... will post any updates here.

                    • 7. Re: Custom SQL
                      Yuriy Fal

                      HI Rahul,

                       

                      Why not just making a calculated field (in every Datasource) -- 

                      like the one suggested by Lukasz -- and use it as a Datasourse Filter?

                      Let Tableau do the rest for you. :-)

                       

                      Yours,

                      Yuri

                       

                       

                      • 8. Re: Custom SQL
                        Łukasz Majewski

                        Hi Rahul,

                         

                        I tested this against sql server and it did work. You say the error said date cannot be compared with int - this makes me believe your db field must be of date type since year([date]) returns int. Can you verify that?

                        1 of 1 people found this helpful
                        • 9. Re: Custom SQL
                          Stephen Rizzo

                          Hi Rahul,

                           

                          With regards to a date table approach - you don't need to worry about a cross-product here. If you joined your data (inner join) to a date table based on your date field, it would be a 1-1 or many-to-one relationship.

                           

                          The bigger concern, after giving this some more thought, is that because you don't have a dedicated date table in your database you would need to get it from another data source using cross database joins. This would force you to have the date table outside of the custom sql block, and since the date filter has to be on the date-formatted date field, the parameter filter would have to be applied to the second data source only. I would be concerned that Tableau might try to run the code in your custom sql block before filtering based on the inner join, in which case you would run into performance issues.

                          I would suggest testing this approach at least, as using a date table would give you significant flexibility in how you leverage your dates.

                          • 10. Re: Custom SQL
                            Rahul Upadhye

                            Łukasz Majewski Łukasz Majewski

                            This morning the data type was changed from date to int & i realized it was the reason for error last week.

                            Now its working perfectly.

                            Thanks to everybody for pouring in valuable suggestions.