Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.

Run SQL query with existing table prepared by alteryx

troy_mech
8 - Asteroid

In my alteryx workflow, I have three tables. I have tried joining the three tables like how it was deigned in MS Access DB but no luck. I would like to join three data set with sql query copied from MS Access SQL view window. Is it possible to run copied query in alteryx with existing table.

6 REPLIES 6
joshuaburkhow
ACE Emeritus
ACE Emeritus

@troy_mech 

 

It's been forever since I've used Access but maybe this is a useful post https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Access-SQL-conversions/td-p/642034

 

Maybe @GoldenDesign04 or @DanielG could provide more guidance? 

Joshua Burkhow - Alteryx Ace | Global Alteryx Architect @PwC | Blogger @ AlterTricks
Qiu
20 - Arcturus
20 - Arcturus

@troy_mech 
I am not SQL expert but I have heard  that Access Query and SQL Query are not using the same syntax entirely.

For instance, MS Access requires parentheses around each join, which is not the case of SQL Query.

So you need to make sure the Access Query you made in Access will work as SQL query as well.

Here is the converter, havenot test it, but may help.

https://www.codeproject.com/Articles/5246516/MS-Access-Queries-to-SQL-Server-Converter

DanielG
12 - Quasar

@troy_mech 

 

Post the SQL and I can take a look.  Similar to the post that @joshuaburkhow linked in his comment, I am sure it can be translated rather easily.

 

You are correct that the SQL is slightly different in Access than it is in Alteryx.

 

I didnt check out @Qiu 's suggestion which might also work, but depending on the complexity of your query (or the # of queries you need to convert) might not be worth the trouble of building that out.

 

MS Access is like the mafia.  I try to leave it in my past, but it keeps pulling me back in...  😀

 

GoldenDesign04
8 - Asteroid

I agree with @DanielG the SQL can be converted.

It varies wildly on what needs to be changed in the syntax from server to server. In Oracle, the schema and tables, in IBM Netezza the encapsulation is quite different and on depending on the syntax the server is 

Best to see the query so we can walk you through what syntax might be the bothersome issue.
I have been converting too many MS Access queries as of late. It seems like I can't get myself out of it. But my troubles can be your gain, so @troy_mech   add the additional detail so we can help!

troy_mech
8 - Asteroid

Hi @GoldenDesign04  I'm trying to convert MS Access query to alteryx workflow. 

 

SELECT FinalData.*, Client.[ID Name], Client.Type, Client.Collateral, Client.[Payment No] AS Payment, IIf([Group Relationship]="Y","Y","N") AS [Group Rel], RSDBdata.Business, RSDBdata.Manager, RSDBdata.business_type_2 AS [BusinessType 2], 
FROM (FinalData LEFT JOIN RSDBdata ON (FinalData.code = RSDBdata.[Sort Code1]) AND (FinalData.refer_ID = RSDBdata.refer_ID_no)) LEFT JOIN Client ON (FinalData.refer_ID = Client.ReferID) AND (FinalData.code = Client.[Sort code]) AND (FinalData.ID_number = Client.CustID)
WHERE (((FinalData.ID_number) Is Not Null));

 
DanielG
12 - Quasar

@troy_mech 

 

Does this do what you need?

 

Hopefully I got it at least close to correct for you.  It is 9:45 on a Monday morning, so I cant fully guarantee my results.  🙂

 

Good luck.

Labels