Guide to Query Builder

The section provides you with comprehensive guidance and support on navigating and utilizing the Infoveave Query builder features effectively. The Query Builder enables visual SQL query creation, including selecting columns, specifying tables, and defining JOINS, WHERE, GROUP BY, HAVING, and ORDER BY conditions with an easy-to-use interface.

The Infoveave Workflow layout is divided into three- the Tables and Column Panel, Query Builder, and Query Setup Panel. By understanding these components, you will be able to efficiently navigate and utilize the query builder.

Table and Column Panel

• Datasource The Datasource option helps you make required changes to the Datasource such as adding multiple tables and establishing relationship between them.
• Search Option The Table and Column Panel includes a search option that allows you to quickly find specific tables or columns from the tables by entering column or activity names in the search bar. This helps streamline the activity selection process, especially when working with a large number of activities.
• Drag-and-Drop Feature The Table and Column Panel offers a convenient drag-and-drop functionality. You can simply select and drag the desired column from the Panel onto the query builder. This intuitive feature simplifies the process of adding activities to your Workflow design, enhancing productivity.
• Select All Checkbox the table in the Table and Column Panel allows to add all the columns from a table to the query builder in a single go.
• Change Datasource The Table and Column Panel allows you to select a different Datasource than the already selected one any time.

Note

You can select a column just by double-clicking on the required column name.

Query Builder

 The query builder interface allows you to create SQL queries visually. Here is a breakdown of the different sections and how to use them

Select

Choose the columns you would like to include in the query. To select columns follow these steps

1. Click on the + Add column button and select the columns from the dropdown that appears. 
2. Manually drag and drop the columns or double-click on the column names to add them.
3. To define the column aggregation type and alias, click on the column name and add the aggregation type and alias to the column name. Example “DISTINCT(T2.category_15)”, where DISTINCT is the aggregation type, T2 is the table alias, and category_15 is the column name.
4. To clear the changes made to the column, including the aggregation and the alias, click on the Clear changes icon.
5. To view the created query, click on the Generate Query icon .
6. To execute the query created, click on the Execute icon.
7. Click on the Save icon associated with the column name to save the changes made.

From

Specify the tables from which to retrieve the data.

1. The table name will be automatically added based on the column selected.
2. To change the table, click on the table name and select the required table from the available options. You can also change the table alias as required.
3. Click on the associated Save button to save the changes made.

Join

The Join section in the query builder allows you to join two or more tables with key columns.

1. Click on the +Add Table button, to select the available tables to perform the JOIN operation.  
2. Once the table is selected, click on the selected table to define the join type from INNER JOIN, FULL JOIN, LEFT JOIN and RIGHT JOIN and the table alias. By the default the table alias is assigned based on the order of the table.
3. Select the key columns from the tables for the JOIN operation.Switch from column to value when applying fixed conditions when selecting another column or entering the value for comparison.
4. To switch from column option to expression, click on the associated fx option.
5. To add a rule/condition to the JOIN statement, click on the + Add rule icon. You switch from column to value when applying conditions.
6. Click on the associated + Add group icon to add nested condition/group to the JOIN statement. Switch between NOT/AND/OR condition by clicking on the respective buttons when adding rules or nested conditions/groups. To change the operator for rules and nested groups click on the operator symbol (=).
7. Click on the Clear changes icon to remove any changes made to the JOIN statement.
8. Click on Save to save the individual statements, rule and condition.

Note

If you have selected columns from multiple tables, the system automatically adds the tables for the join operation. The types of joints available depend on the Datasource type.

Where

Apply WHERE condition to return the values that meet the specified conditions.

1. To add a WHERE condition, click on the associated + icon, to select the available column to apply the WHERE condition.
2. Once the column is selected, click on the operator function to change it accordingly
3. To switch from column option to expression, click on the associated fx option.
4. To add a rule/condition to the WHERE statement, click on the + Add rule icon.
5. Switch from column to value when applying fixed conditions when selecting another column or entering the value for comparison.
6. Click on the associated + Add group icon to add nested condition/group to the WHERE statement.
   • Switch between NOT/AND/OR condition by clicking on the respective buttons when adding rules or nested conditions/groups.
   • To change the operator for rules and nested groups click on the operator symbol (=).
7. Click on the Clear changes icon to remove any changes made to the WHERE statement.
8. Click on Save to save the individual statements, rule and condition.

Group by

Group rows that have the same values in specified columns into aggregated data.

1. To add a GROUP BY condition, click on the + Add column button.
2. Select the column you want to GROUP BY. You can add multiple columns to GROUP BY.

Having

Apply HAVING function defined by the GROUP BY clause, used with aggregate functions (e.g., COUNT, SUM).

1. To add a HAVING condition, click on the associated + icon, to select the available column to apply the HAVING condition.
2. Once the column is selected, click on the operator function to change it accordingly
3. To switch from column option to expression, click on the associated fx option.
4. To add a rule/condition to the HAVING statement, click on the + Add rule icon.
5. Switch from column to value when applying fixed conditions when selecting another column or entering the value for comparison.
6. Click on the associated + Add group icon to add nested condition/group to the HAVING statement.
   • Switch between NOT/AND/OR condition by clicking on the respective buttons when adding rules or nested conditions/groups.
   • To change the operator for rules and nested groups click on the operator symbol (=).
7. Click on the Clear changes icon to remove any changes made to the HAVING statement.
8. Click on Save to save the individual statements, rule and condition.

Order by

Order rows that have the same values in specified columns into aggregated data.

1. To add an ORDER BY condition, click on the + Add column button.
2. Select the column you want to ORDER BY. You can add multiple columns to ORDER BY.

Note

Enter the value required under Take and Skip to include or skip the number of rows. Take option limits the number of rows to be included. Skip option allows you to skip the first n number of rows.

Query Builder Setup Panel

Within the Setup Panel, you can carry out essential settings such as the query name, edit Datasource and switch views.

Naming Providing meaningful information in these fields helps you easily identify and differentiate your query.

Tip

For a list of all SQL keywords and clauses, click on Ctrl+Space.