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.

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.

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?

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

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.

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

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.

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.

Does this help?

SELECT  *

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

  '

  PUT YOUR WORKING CTE QUERY HERE

  '

)

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.