1 2 Previous Next 25 Replies Latest reply on Aug 15, 2013 7:05 AM by Kevin I

    Subqueries in Custom SQL Connections

    Kevin I

      I'm trying to do a SQL Subquery from a SQL Server in the Custom SQL connection in Tableau 8.  I am able to run my query in both AQT and Access SQL passthrough.  Is there something I can do to get it to work in the custom SQL in Tableau 8?  Below is the simplified shell of my query:

       

      with TESTABLE as (

      SELECT *

      FROM XYZ

      WHERE State = MA

      Group by Customer)

       

      select * from TESTABLE where _____

       

      Any help is greatly appreciated!  I have been messing with this for far too long.

        • 1. Re: Subqueries in Custom SQL Connections
          Toby Erkson

          DISCLAIMER:  I'm not an SQL expert.

          I mimicked your code in my AQT against a DB2 source and it failed when using the GROUP BY.  When I removed it the query ran fine.  Here's what worked for me:

          with TESTABLE as (
          select *
          from CDW.PARTY_S1
          where party_type_desc='PERSON' )
          
          Select * from TESTABLE where FIRST_NAME='BOB'
          

           

          Just because it works in an outside query tool doesn't necessarily mean it will work in Tableau's Custom SQL writer.

          You shouldn't need the GROUP BY clause; let Tableau group things as it sees fit.

          Don't get fancy with your SQL.  Dumb it down.

          • 2. Re: Subqueries in Custom SQL Connections
            Matt Lutton

            Toby is correct.  No "group by" or "order by", etc. in Tableau's Custom SQL--you'll get an error every time, which can be confusing to experienced SQL users.

            • 3. Re: Subqueries in Custom SQL Connections
              Kevin I

              Hi All,

               

              Thanks for your response.  I attempted to remove the “group by” and still received errors.  For more context, here is a more complete example of my SQL and error message.  Again, I really appreciate the help with this!

               

               

              with TESTABLE as (

               

              select field1,Field2,field3, field4,

              max(case when field5  ='criteria1' then field6 else null end) as newfield1,

              max(case when field5  ='criteria2' then field6 else null end) as newfield2,

              max(case when field5  ='criteria3' then field6 else null end) as newfield3,

              max(case when field5  ='criteria4' then field6 else null end) as newfield4,

              max(case when field5  ='criteria5' then field6 else null end) as newfield5,

              max(case when field5  ='criteria6' then field6 else null end) as newfield6,

              max(case when field5  ='criteria7' then field6 else null end) as newfield7,

              max(case when field5  ='criteria8' then field6 else null end) as newfield8,

              max(case when field5  ='criteria9' then field6 else null end) as newfield9,

               

              from Table1

              where field15>'1/1/2013'

              group by field1,Field2,field3, field4)

              );

               

              select * from TESTABLE where newfield3 is not null and newfield4 is not null and newfield6 is null and newfield7 is null

               

               

              The specific errors are as follows:

               

              Database error 0x80040E14: Incorrect syntax near ')'.

              Database error 0x80040E14: Incorrect syntax near the keyword 'from'.

              Database error 0x80040E14: Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.

              Database error 0x80040E14: Incorrect syntax near the keyword 'with'.

               

              Unable to connect to the server "REDACTED". Check that the server is running and that you have access privileges to the requested database. –This error is false – I can connect just fine through other, more simple, SQL statements.

              • 4. Re: Subqueries in Custom SQL Connections
                Russell Christopher

                Hey Kevin -

                 

                Can you share with us why you're attempting this? You seems like you're trying to solve a specific problem with this approach - but there may be a better way than using nested subqueries....

                 

                So what's the problem you're trying to solve?

                • 5. Re: Subqueries in Custom SQL Connections
                  Kevin I

                  I am first taking some criteria to determine a bunch of different  timestamps that I will need to assess.

                   

                  For example, when a Sale happens, I want a field that gives me the timestamp associated with the SALE.  Let’s say that there are roughly 10 different “actions” that would each have its own timestamp.  My subquery seeks to match the criteria for each “action” with the system timestamp associated with it.

                   

                  Then, in my outer query, I am trying to solve for a specific set of scenarios.  So when the timestamp for action 1 is null but action timestamps, 3,4,5 are valid, I would like to display my primary key (we’ll call this customer #) and order #.

                   

                  It would look like this for fields where the above logic is true.

                  Customer

                   

                  Order #

                   

                  1234321

                   

                  82984474393274

                   

                  87466467

                   

                  10009847646327

                   

                   

                   

                   

                   

                   

                   

                   

                  I would like to do this in my custom sql because when I try it in Tableau, it limits how I can display these things along with insights from other data connections, as this data is specific to an “order” and my other data is mostly at a higher level ie “customer”.

                  • 6. Re: Subqueries in Custom SQL Connections
                    Dru Duos

                    I've found that putting the semicolon in a SQL script fails in Tableau

                    • 7. Re: Subqueries in Custom SQL Connections
                      Russell Christopher

                      Sounds like a scenario where Data Blending might be useful? Are you familiar with it?

                       

                      Essentially you'd have two data sources - one to pull the actions/timestamps, one to pull scenarios. Then, you (sort of) "join" the results using a common field directly in Tableau.

                      • 8. Re: Subqueries in Custom SQL Connections
                        Kevin I

                        I’m not really familiar with blending – it’s not just identifying common keys between the two tables in the edit relationships section?

                        • 9. Re: Subqueries in Custom SQL Connections
                          Russell Christopher

                          I didn't see the full query you pasted in above - Since it doesn't really look like there's a common field between the sets of data that we could "join" on, I don't think blending will work for you.

                           

                          However, I got something similar to what you're trying to do working against SQL Server just now:

                           

                          SELECT a.* FROM

                            (SELECT

                                f1,

                                f2,

                                date,

                                max(case when f3 > 1 then f3 else null end) as newfield

                            FROM

                                table1

                            WHERE

                              date > '1/1/2012'

                            GROUP BY f1, f2, date

                             ) a

                          WHERE a.newfield is not null

                          • 10. Re: Subqueries in Custom SQL Connections
                            Richard Leeke

                            I haven't read the full thread carefully (from my phone) but I'll just point out the main issue with the original custom SQL.

                             

                            The problem is that you are trying to use a common table exclusion (the WITH clause). That is only allowed to appear at the outer level in the eventual query, but Tableau is going to take your custom SQL and wrap the whole expression as a sub-query, which will not be allowed.  If you re-express the query taking the CTE inline it may work.

                            • 11. Re: Subqueries in Custom SQL Connections
                              russell.spangler

                              If your CTE works directly in SQL then you should be able to wrap it in an openrowset and send the query to SQL (this at least works for the source database is MSSQL).  I believe Tableau doesn't like advance SQL commands, like variables, loops, IF statements etc.

                               

                               

                              http://technet.microsoft.com/en-us/library/ms190312.aspx

                               

                              This works if you swap the correct pieces into the query and use it as a custom SQL connection in Tableau.

                               

                              SELECT  *

                              FROM OPENROWSET( 'SQLNCLI','Server=[servername];Trusted_Connection=yes;',

                                'with CTECal as (

                              select COUNT(*) as Records, [FIELD]

                                from

                                [TABLE] with (nolock)

                                group by [FIELD]

                              )

                              select * from CTECal'

                              )

                               

                              I also don't think you need a CTE to get the output are are wanting to get which could fix the problem of trying to use a CTE.

                              • 12. Re: Subqueries in Custom SQL Connections
                                Kevin I

                                Can anyone show me how to see the wrap?  Or can someone format this for me?  I am completely lost…

                                • 13. Re: Subqueries in Custom SQL Connections
                                  russell.spangler

                                  Does this help?

                                   

                                  SELECT  *

                                  FROM OPENROWSET( 'SQLNCLI','Server=[servername];Trusted_Connection=yes;',

                                    '

                                    PUT YOUR WORKING CTE QUERY HERE

                                    '

                                  )

                                  • 14. Re: Subqueries in Custom SQL Connections
                                    Richard Leeke

                                    To see the actual queries Tableau Is sending to the database (ie including all of Tableau's wrapping) you can either look in the log files in your Tableau repository or if you have access to the SQL server you can run the SQL profiler to see them. That let's you see exactly what SQL server is complaining about.

                                    1 2 Previous Next