3 Replies Latest reply on Aug 17, 2018 10:55 AM by Vinnie Ahuja

    Custom SQL with Union All Need to Split

    Dileep Reddy

      Hi All,

       

      I wrote a complicated SQL with 6 combinations of data Union All's in teradata with Start & End date as Parameters. when i included this sql in Custom sql, it is taking almost 30 mins to load the dashboard.

       

      And So, I'm looking for other options to switch from Custom SQL's. Each from clause has same tables but different join conditions.

       

       

      My SQL looks like :

       

      Select Col1,Col2,Col3,Col4,'A' As Col5, 'AA' AS Col6,Col7,Col8,Col8 from ( Select * from table1) fo

      Union All

      Select Col1,Col2,Col3,Col4,'B' As Col5, 'BB' AS Col6,Col7,Col8,Col8 from ( Select * from table1) fo1

      Union All

      Select Col1,Col2,Col3,Col4,'C' As Col5, 'CC' AS Col6,Col7,Col8,Col8 from ( Select * from table1) fo2

      Union All

      Select Col1,Col2,Col3,Col4,'D' As Col5, 'DD' AS Col6,Col7,Col8,Col8 from ( Select * from table1) fo3

      Union All

      Select Col1,Col2,Col3,Col4,'E' As Col5, 'EE' AS Col6,Col7,Col8,Col8 from ( Select * from table1) fo4

       

      Report always run for the single combination, either on 'A','AA' or 'B','BB' or 'C','CC', etc.,

       

      So, what i thought is Instead of executing the whole query with Union All.

       

      Can we execute the required sql combination alone based on the Input selection ?

           1) with Initial SQL or

           2) Any Other option like Java Script API, where we will create a Java script code and plug that to Tableau. Ultimately which should perform the  sql execution based on the Input and send           the data to Tableau.

           3) Or do we have any other options. please advice.

       

      Thanks for your valuable time on spending this.

        • 1. Re: Custom SQL with Union All Need to Split
          Naveen B

          hi Dileep,

           

          Create a 2 parameters and pass them in custom sql

           

          parameter 1 : A,B,C,D,E

          Parameter 2: AA,BB,CC,DD,EE

           

          write custom sql like this

           

          Select Col1,Col2,Col3,Col4,<Parameter.parameter1> As Col5, <Parameter.parameter2> AS Col6,Col7,Col8,Col8 from ( Select * from table1)

           

          Hope this helps kindly mark this answer as complete and helpful so that it will help others

           

          BR,

          NB

          • 2. Re: Custom SQL with Union All Need to Split
            Dileep Reddy

            Hi Naveen,

             

            i don't think we can do this. because each from clause has different join conditions. that is what my worry.

            • 3. Re: Custom SQL with Union All Need to Split
              Vinnie Ahuja

              Hi Dileep,

               

              The viability of this will depend on how quickly your data changes and how much of it there is, but it is worth suggesting....

               

              You could try pre-selecting the earliest conceivable start date and the latest conceivable end date a user may have and hardcode these into the SQL.  You could then create an extract and just use the parameters within Tableau (i.e. do not pass them to DB via custom SQL).  If your data allows you to do this, this should help tremendously with performance.

               

              Cheers

              Vinnie