What SQL functions or commands work in Alteryx?

[Ayelavan]

I know there is a standards around SQL one such being ANSI (American National Standards Institute) and clients such as Oracle and Microsoft SQL server add their own commands which are specific to them but its not clear to me what SQL framework Alteryx uses. 

For instance, the SQL editor query

"select top 100 * from table;" 

seems to work in Alteryx but I know that this command does not work on all SQL clients (oracle for instance). Is there anywhere where I can find a comprehensive list of SQL commands and functions that are supported by Alteryx? 

Also do the in database tools work by sending SQL commands to the database they are connected to and only returning the result of the query? If so are the SQL commands available to us in the in-database dependent on what SQL client the connection is with?

[danrh · Accepted solution]

Ayelavan, someone else more knowledgeable might need to speak here, but it is my understanding that SQL queries are dependent on the database you're querying (whether or not you're using In-Database tools) and tools like the Input data only return the results.  So if you're hitting a SQL Server, follow SQL Server syntax.  If you need an Oracle database, use Oracle syntax. 

As a side perk, because the query is executed on the database side before it is brought into your workflow, you can speed up an Alteryx flow considerably by adding one or two filters to your SQL query.  Another bonus is that if you already have a query written out, you don't need to worry about changing syntax, just drop the query as is into the Input tool.

[Ayelavan]

I have done some digging confirmed that the is related to the database you have connected to.