Hi, Stephen. Have you tried using the semicolon character ; before the with in your custom SQL?
Using the semicolon character before the with is a known syntax weirdness of T-SQL.
Let us know if this makes a difference.
Matt Lutton Thanks - I sent them an email
Phillip Burger I tried adding the ; but still gave me the error. I found another thread with a similar issue.
What I'm trying to do is I have data in one table that has duplicate Master Numbers but I only need the first (oldest) one:
Master Number -- SOP Number -- Date -- Sales $
123 -- 1250 -- 1/10/14 -- $100
123 -- 1251 -- 1/13/14 -- $50
124 -- 1301 -- 1/09/14 -- $25
Does anyone know of a workaround that'd get me the same result?
What about a nested subquery?
I'm kind of a beginner at SQL. What would that look like?
1 of 1 people found this helpful
The reason you are having the problem is that Tableau only accepts basic queries. You can use SQL, but not T-SQL. This means you can't set up variables etc before the query.
If you want just the oldest record, you should be able to use a min against sql server.
Using the example data you posted, I would try something like:
SELECT A."Master Number", A."SOP Number", A."Date", A."Sales $" FROM "Table" A INNER JOIN (SELECT B."Master Number", MIN(B."Date") FROM "Table" B GROUP BY B."Master Number") C ON A."Master Number"=C."Master Number" AND A."Date"=C."Date"
The subquery there just creates a table with the earliest date for each master number.
Bare in mind this relies on there only being one record for each master number for each day.
Appreciate the help guys
I'm pleased that so many knowledgeable customers were able to give you great guidance in this thread! I wanted to point out in advance that Tableau does not officially support connecting to a Common Table Expression within Custom SQL, and while this functionality may work in some cases we cannot guarantee it will meet your needs or continue to work in future releases. If this is an important use case to you or your business, please be sure to inform your Sales rep at Tableau so they may associate this need for functionality with the opportunity that your business represents.