-
1. Re: Subqueries in Custom SQL Connections
Toby ErksonAug 14, 2013 8:15 AM (in response to Kevin I)
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 LuttonAug 14, 2013 8:30 AM (in response to Toby Erkson)
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 Aug 14, 2013 9:27 AM (in response to Matt Lutton)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 Aug 14, 2013 9:47 AM (in response to Kevin I)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 Aug 14, 2013 10:01 AM (in response to Russell Christopher)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 Aug 14, 2013 10:05 AM (in response to Kevin I)I've found that putting the semicolon in a SQL script fails in Tableau
-
7. Re: Subqueries in Custom SQL Connections
Russell Christopher Aug 14, 2013 10:09 AM (in response to Kevin I)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 Aug 14, 2013 10:18 AM (in response to Russell Christopher)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 Aug 14, 2013 10:43 AM (in response to Kevin I)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 Aug 14, 2013 12:18 PM (in response to Russell Christopher)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 Aug 14, 2013 12:52 PM (in response to Kevin I)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 Aug 14, 2013 12:49 PM (in response to russell.spangler)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 Aug 14, 2013 12:52 PM (in response to Kevin I)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 Aug 14, 2013 1:10 PM (in response to russell.spangler)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.