1 2 Previous Next 25 Replies Latest reply on Aug 15, 2013 7:05 AM by Kevin I Go to original post
      • 15. Re: Subqueries in Custom SQL Connections
        Kevin I

        This removed all of my errors – but a new one has cropped up.

         

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

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

          So it sounds like the syntax is wrong somewhere near where TableauSQL is being referenced.  Is TableauSQL the server name? table name? a field name? I can help, but I need to either see the query, or understand where TableauSQL is being used to guess at what the error is.

           

          Does the CTE part work directly in SQL? Also don't put brackets around the servername, that was just to show where to swap your information in.  My first guess is your actual SQL server name isn't TableauSQL?

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

            I had already removed the brackets.  I don’t see “TableauSQL” actually written in my SQL at all.  It’s not any of the things you suggested (according to my code).  I’m not sure what you mean by CTE, but if that means the actual SQL query that I started with, yes, that part works using other applications (Access, AQT).

             

            I’m not sure how to share my actual query in a manner that would keep it confidential.  I attempted to mimic it exactly in my earlier posts, removing specific table, server and field names.

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

              I see this ahead of my query when I check a data source in the repository

               

              Select

               

              That might explain the error below.

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

                I explained what a CTE is in my earlier post (except that my phone changed extension to exclusion).

                 

                We really need to see the form of the full query Tableau generated or we can't help you.  Is easy to anonymous it if you need to.

                • 20. Re: Subqueries in Custom SQL Connections
                  Jonathan Drummey

                  Jumping in a little late here…I don't know about the CTE expressions, however I can see a likely problem here:

                   

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

                  from Table1

                   

                  The comma before the FROM statement can blow up a query.

                   

                  Also, GROUP BY, ORDER BY, and IIF will all work for JET sources, and I presume other ones since JET is pretty much lower-than-lowest common denominator.

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

                    Using the keyword WITH then creating a query is a "CTE" (WITH common_table_expression (Transact-SQL)), so yes I'm talking about the query.  Is your data connection called TableauSQL?  It could be as simple as a missing quote or comma or extra parenthesis somewhere.  What kind of database are you connecting too? Microsoft SQL? Access?

                     

                    Just a FYI the query in the first post won't work, because the MA isn't wrap in single quotes and you need some kind of aggregation (like MAX) and a field to perform a group by.  The query in reply #3 doesn't work because of the extra comma after "newfield9" in the SELECT portion, and the extra parenthesis after the group by is causing a problem.


                    Maybe mock up generic queries and generic data and provide those with the queries tested directly in SQL, so we know the queries are good.  It sucks but its hard to figure out syntax errors without seeing the query.

                     

                    I think your last post got cut off.

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

                      Attached is a data mock up, my SQL as executed outside of Tableau, and my results.

                       

                      I am looking for the log files, but cant seem to locate them.

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

                        In Desktop go File->Repository Location to find where your repository is. Browse to that location and look in the Logs folder. Refresh your view to force the error and then look in the most recently touched file (probably log.txt if you only have one instance of Tableau running).

                         

                        Look at the bottom of the log and you should find the actual SQL Tableau sent and the error message back from SQL Server.

                         

                        You will probably see a statement like:

                         

                        SELECT TOP 1 *

                        FROM (

                        Your custom SQL statement

                        ) AS [TableauSQL]

                         

                        That is just Tableau executing the first of many statements it uses to explore the structure of your connection (and the exact syntax will vary depending on what database you are connected to).

                         

                        A CTE normally has to be at the outer level - though it sounds as if the opensrowset trick has got you past that.

                         

                        Hopefully seeing the SQL that it is complaining about will give you a clue.

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

                          The query you provided in the mock up does not work.  The WHERE clause in the CTE is wrong, because ord_ts isn't a column name, should be ORDER_TS and in the WHERE clause on your final query, C is null doesn't work because C isn't a column name.  Anyways I edited the query to make it work.

                           

                          SELECT  *

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

                            '

                          with TESTABLE as (

                          select location,order_no,order_ts,

                          max(case when ord_prcs_stat_c  =''CP'' then updt_ts else null end) as CP_TS,

                          max(case when ord_prcs_stat_c  =''GW'' then updt_ts else null end) as GW_TS,

                          max(case when ord_prcs_stat_c  =''N'' then updt_ts else null end) as N_TS,

                          min(case when ord_prcs_stat_c  =''W'' then updt_ts else null end) as W_TS,

                          max(case when ord_prcs_stat_c  =''PU'' then updt_ts else null end) as PU_TS,

                          max(case when ord_prcs_stat_c  =''Alert'' then updt_ts else null end) as A_TS,

                          max(case when ord_prcs_stat_c  =''S'' then updt_ts else null end) as S_TS,

                          max(case when ord_prcs_stat_c  =''GH'' then updt_ts else null end) as GH_TS,

                          max(case when ord_prcs_stat_c  =''RFH'' then updt_ts else null end) as RH_TS,

                          max(case when ord_prcs_stat_c  =''E'' then updt_ts else null end) as E_TS,

                          max(case when ord_prcs_stat_c  =''C'' then updt_ts else null end) as C_TS

                          from [database].dbo.[TableName]

                          where order_ts>''5/12/2013''

                          group by location,order_no,order_ts

                          )

                           

                          select * from TESTABLE where E_TS is not null

                          '

                          )

                           

                          When referencing strings inside of an openrowset you need to put double quotes around the string.  The easiest thing to do is test the entire query within SQL (with the openrowset command), if that returns an output then it should work within Tableau.  (If this doesn't work, follow Richard's advice and provide the log).

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

                            Yeah that’s just my bad – I was replacing actual column names to keep information a little more confidential.  It works, just made a few mistakes in my attempt to “encrypt” actual names.  Working on the log file right now.

                            1 2 Previous Next