The Choose Table or Specify Query window displays when you select an input containing multiple tables. You can select tables and construct queries to pull data from relational databases.
Alteryx Designer uses Active Query Builder, a third party component for building complex SQL queries. For more information, see the Active Query Builder User's Guide.
The Tables tab displays all of the tables and views contained within the specified relational database. To refresh the cached list of tables, click Refresh. To select a table:
Select a table/view in the list.
Click OK to open the table, or select another tab to build a query directly from this table/view.
You can also bypass this tab and proceed to the subsequent tabs to build a query utilizing multiple tables/views.
Visual Query Builder
Visual Query Builder caches and loads tables from databases only when the table view is opened. The Visual Query Builder tab consists of three sections for querying tables and creating data relationships between tables.
Visual Query Builder may load slowly with large Hive, Impala, or Spark database clusters. Use the Tables tab or SQL Editor tab for better performance when connecting to these 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 auto-populates.
To join objects, click and drag a connection arrow between the fields of each object displayed in the Main section.
To remove a table view from the Main section, click the X button for that table.
Grid: The grid auto-populates based on your specifications in the Main section. You can edit this grid to modify the query. The grid components are:
Output: Determines field output. Select the check box to output the field and its corresponding criteria.
Expression: This drop-down list displays, by default, the selections from the Main section. You can expand the drop-down list to select from all fields from all tables.
Aggregate: Drop-down list displays aggregate functions on the specified data field, such as sum, average, min and max.
Alias: Accepts a user-specified string to assign an Alias to the original field name.
Sort Type: Drop-down list displays the type of sort to execute: none (blank), Ascending or Descending.
Sort Order: Drop-down list displays the number of expressions in the grid, determining the order of the sort.
Grouping: Determines if a grouping process should be executed. When checked, grouping will occur.
Criteria for Grouping: When the Grouping check box is selected, the drop-down list displays. Select either For values or For groups.
Criteria: Accepts a user-specified string to assign a criterion element to the query.
Or...: Accepts a user-specified string to assign an OR clause to the query.
Table List: 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 click and drag the table name into the Main section.
Stored Procedures
Click this tab to access stored procedures. The Alteryx engine
is not used when executing a stored procedure. The execution happens entirely
on the database server.
Stored procedures are only supported for Microsoft
SQL Server accessed using standard input tools.
The Stored Procedures tab contains two 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 will translate the query
language from the Visual Query Builder tab into the SQL-equivalent syntax.
Likewise, a change in the SQL Editor will automatically change 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.