Alteryx Designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
Don't forget to submit your entry for the Excellence Awards by October 30! | Need more information about the program? Check out the blog here

Getting errors when using common table expressions with "Connect In-DB" tool

Highlighted
6 - Meteoroid

I can build the query using the visual query builder and the query runs fine in excel but, when I use a browse tool or try and use that query in a workflow, it gives me the error:

 

"...contains more than the maximum number of prefixes"

This error seems to only happen when using common table expressions and, sometimes, subqueries. The sql code works perfectly fine if I copy it from Alteryx and paste it into excel, it just doesn't seem to work in Alteryx.  

 

The sql code for an example query that is getting this error but works fine in excel is below.  

 

Any help would be greatly appreciated. 

WITH Email
AS (SELECT
b.EmailAddress,
c.DisplayName,
c.UserCode,
d.PhoneNumber
FROM SQL2.MMPeople.MMP.tblUser_cache a
INNER JOIN SQL2.MMPeople.MMP.tblUserEmail b
ON a.UserEmailID = b.UserEmailID
AND b.IsPrimary = 1
AND b.Deleted = 0
INNER JOIN MMPeople.tblUser_History c
ON c.UserCode = a.UserCode
LEFT JOIN SQL2.MMPeople.MMP.tblUserPhone d
ON a.UserPhoneID = d.UserPhoneID
AND d.IsPrimary = 1
AND d.Deleted = 0
WHERE c.AsOfDate_To = '1/1/3000')
SELECT
A.DisplayName,
Email.EmailAddress
FROM MMPeople.tblUser_History A
INNER JOIN Email
ON A.UserCode = Email.UserCode
WHERE A.AsOfDate_To = '1/1/3000'

Highlighted
16 - Nebula
16 - Nebula

I think currently it is not possible to use CTE within the InDB tools.

 

Taking a very simple case:

WITH Generation  AS
(
    SELECT p.ID, q.Name as Mother FROM dbo.Person p LEFT JOIN dbo.Person q on p.Mother = q.ID
)
SELECT Person.ID, Person.Name, Generation.Mother
FROM Generation INNER JOIN dbo.Person on Generation.ID = Person.ID

and building the simplest workflow:

2018-10-11_21-40-03.png

 

This results in Alteryx running a query like:

WITH "Tool1_2392" AS (SELECT * FROM "With Generation As (Select p"."ID, q"."Name As Mother From dbo"."Person p Left Join dbo"."Person q On p"."Mother = q"."ID) Select dbo"."Person"."ID, dbo"."Person"."Name, Generation"."Mother From Generation Inner Join dbo"."Person On Generation"."ID = dbo"."Person"."ID") 
 SELECT TOP 100 * FROM "Tool1_2392"

This isn't valid SQL. I would raise this with support if you need it.

 

In your example case I would restructure your query to:

SELECT A.DisplayName, Email.EmailAddress
FROM MMPeople.tblUser_History A
INNER JOIN (SELECT b.EmailAddress, c.DisplayName, c.UserCode, d.PhoneNumber
      FROM SQL2.MMPeople.MMP.tblUser_cache a
INNER JOIN SQL2.MMPeople.MMP.tblUserEmail b ON a.UserEmailID = b.UserEmailID AND b.IsPrimary = 1 AND b.Deleted = 0 INNER JOIN MMPeople.tblUser_History c ON c.UserCode = a.UserCode
 LEFT JOIN SQL2.MMPeople.MMP.tblUserPhone d ON a.UserPhoneID = d.UserPhoneID AND d.IsPrimary = 1 AND d.Deleted = 0 WHERE c.AsOfDate_To = '1/1/3000') Email
ON A.UserCode = Email.UserCode
WHERE A.AsOfDate_To = '1/1/3000'

I think this should produce same results but doesnt have the CTE.

 

Highlighted
6 - Meteoroid

This sort-of solves it.  The query you sent does work but CTE's should be supported since you can add them to queries through right-clicking in the visual editor and selecting "Add Common Table Expression".  If they weren't supported, I don't understand why the option would be there.  

 

Using derived tables, like in your answer, would work but I need to join the same query 6 times in 6 different ways so a CTE would be much preferred over having 6 copies of the same query. 

Highlighted
16 - Nebula
16 - Nebula

Agree it is odd that it is included in the visual query builder. I am guessing the builder is just the same as the one for the normal Input data tool.

 

I found a similar post from a couple of years ago: https://community.alteryx.com/t5/Alteryx-Designer-Discussions/common-table-expressions-in-database-w...

 

I can't find any open idea to fix the issue, probably worth raising as an issue.

 

Highlighted
8 - Asteroid

What version are you running? I don't see CTE in that box when I right-click in there...  my version is 2018.3.4.51585

Highlighted
6 - Meteoroid

Ah Snap! is it still an issue with 2019.2 ? Did we raise an issue back then ? It would take me ages to convert CTE into joins with-in Alteryx 

Labels