Get Inspire insights from former attendees in our AMA discussion thread on Inspire Buzz. ACEs and other community members are on call all week to answer!
Alteryx Designer Desktop Knowledge Base
Definitive answers from Designer Desktop experts.How To: Build Queries without using the Visual Query Builder
How To: Build Queries without using the Visual Query Builder
The Visual Query Builder window can take a long time to load because it always loads all column metadata for all tables for all schemas, even if a default database is selected in the database connection. This article will walk through using the In-Database tools as an alternative to using the Visual Query Builder.
Prerequisites
- Alteryx
- A working Database connection
- This works best for connections supported for In-DB as noted in the Supported Data Sources and File Formats
Procedure
The Tables view loads only tables names and no column names for the default database selected and it caches the list. This allows it to load faster than the Visual Query Builder.
- Make the Tables View the default view
- Drag an Input tool on the canvas and connect to a database (it doesn't have to be the database in question, any database will work)
- Click the Tables view and click the checkbox next to "Open Tables view by default" at the bottom left of the window
- Create an In-DB Connection to your database as described here: How To: Create an In-Database Connection
- Use a Connect In-DB tool to connect to the database. To build your query:
- Select the table name in the Tables view. This creates a SELECT * FROM TABLE statement with no criteria to limit the query
- Use the In-DB tools to build the query. e.g. the Select In-DB tool to select only columns you need or the Filter In-DB to remove rows not needed
- Alteryx constructs a SQL query based on the tools on the canvas and sends it to the database
- In the example below, both workflows accomplish the same thing, one through the Visual Query Builder, one using the In-DB tools:
