This removed all of my errors – but a new one has cropped up.
Database error 0x80040E14: Incorrect syntax near 'TableauSQL'.
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?
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.
I see this ahead of my query when I check a data source in the repository
That might explain the error below.
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.
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,
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.
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.
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 *
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.
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.
FROM OPENROWSET( 'SQLNCLI','Server=[Server];Trusted_Connection=yes;',
with TESTABLE as (
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
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).
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.