Choose Table or Specify Query Window
The Choose Table or Specify Query window displays when you select an input with multiple tables. You can select tables and construct queries to pull data from relational databases.
Alteryx Designer uses Active Query Builder, a 3rd-party component, to build complex SQL queries. For more information, go to the User's Guide.
In the Choose Table or Specify Query, there are 4 tabs:
Tables
This tab displays all the tables and views contained within the relational database you specify.
To refresh the cached list of tables, select Refresh.
To choose a table, select a table or view in the list, and then select OK to open the table. You can also select another tab to build a query directly from the current table or view.
Note
You can bypass this tab entirely and proceed to subsequent tabs to build a query that uses multiple tables or views.
Since Designer version 22.1 the table cache in the input tool will be refreshed the first time an existing input tool in a workflow is opened.
Visual Query Builder
Visual Query Builder caches and loads tables from databases only when you open the table view. The Visual Query Builder tab has 3 sections for querying tables and creating data relationships between tables.
Note
Visual Query Builder may load slowly with large Hive, Impala, or Apache Spark database clusters. Use the Tables tab or SQL Editor tab for better performance when you connect to those kinds of databases.
Main
This section displays any selected tables or views from the table list, as well as their data fields. As you select fields, the grid automatically populates.
To join objects, drag a connection arrow between the fields of each object displayed in the Main section.
To remove a table view from the Main section, select the "X" button for that table.
Grid
The grid automatically populates based on your specifications in the Main section. You can expand the dropdown list to select from all fields from all tables.
Output determines field output. Select the check box to output the field and its corresponding criteria.
Expression displays the selections from the Main section by default. You can expand the dropdown list to select from all fields from all tables. Use the ellipsis (...) button to refresh or update the field list.
Aggregate displays aggregate functions on the specified data field, like sum, average, minimum, and maximum.
Alias accepts a user-specified string to assign an alias to the original field name.
Sort Type displays the type of sort to execute: none (for example, blank), Ascending, or Descending.
Sort Order displays the number of expressions in the grid, which determines the order of the sort.
Grouping determines if a grouping process should be executed. If you check the box, grouping occurs.
Criteria for Grouping displays after you've selected the Grouping check box. Select either For values or For groups.
Criteria accepts a user-specified string to assign a criterion element to the query. The ellipsis (...) button serves as a refresh/update for the field.
Or . . . accepts a user-specified string to assign an "OR" clause to the query.
Table Lists
The table list displays all of the tables and views that make up the relational database. To select a table, either double-click on the name displayed in the list or drag the table name into the Main section.
Stored Procedures
Select this tab to access stored procedures.
Note
You don't use the Alteryx engine when executing a stored procedure. The execution occurs entirely on the database server.
Stored procedures are only supported for Microsoft SQL Server, Oracle, or SAP HANA. Access stored procedures by using the standard input tools.
This tab contains 2 panes:
The left pane displays all the stored procedures for the selected database. Once you select a stored procedure in the left pane, its parameters are visible in the grid on the right pane.
The right pane displays a grid of the parameters of the stored procedure. Note that...
You must enter the Value to be returned.
When being used in an input, the Value must represent a value within the database.
When being used to update via the Dynamic Input tool, the Value should be unique so that it is easily replaced via the Update Stored Procedure mode.
SQL Editor
The SQL Editor displays your query from the Visual Query Builder tab in Structured Query Language. The SQL editor translates the query language from the Visual Query Builder tab into the SQL-equivalent syntax. Likewise, a change in the SQL Editor automatically changes the query components of the Visual Query Builder.
Alternatively, you can enter your own query into this box, bypassing the Visual Query Builder. You can also add or omit elements contained in this box to execute the desired query.