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.

Layout

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

• 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 How to use

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 located on the top right corner of the query builder. 5. To view the created query, click on the Generate Query icon located on the top right corner of the query builder. 6. To execute the query created, click on the Execute icon located on the top right corner of the query builder.
2. 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.
  How to use

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.
2. 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.
  How to use

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, OUTER, LEFT and RIGHT 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.
  How to use

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.
  How to use

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).
  How to use

3. To add a HAVING condition, click on the associated + icon, to select the available column to apply the HAVING condition.
4. Once the column is selected, click on the operator function to change it accordingly
5. To switch from column option to expression, click on the associated fx option.
6. To add a rule/condition to the HAVING statement, click on the + Add Rule icon.
7. Switch from column to value when applying fixed conditions when selecting another column or entering the value for comparison.
8. 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 (=).
9. Click on the Clear Changes icon to remove any changes made to the HAVING statement.
10. 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.
  How to use

11. To add an ORDER BY condition, click on the + Add Column button.
12. 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.

• Name Provide meaningful information in these fields helps you easily identify and differentiate your query.
• Edit Datasource The edit Datasource option helps you make required changes to the Datasource such as adding multiple tables and establishing relationship between them.
• Switch View The switch view option allows you to switch form the query builder to the query editor for advanced users. 

Tip

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