Building Queries (with examples)

The following examples of creating queries will be extremely useful for those who are not familiar with the SQL basics. Models building is an auxiliary tool that is not necessary for creating requests. If you manage the SQL basics, you can make your request manually in the Content field on Query page.

To fully understand the examples listed below it is advisable to study "User Queries" chapter first.

Example 1

Query: Get the list of corporate experts with all data sorted by experts’ names.

1. Drag&drop EMPLOYEES table at the working area

2. Select the asterisk “*” (to display all table fields) and the field EMP_NAME (needed for sorting). After these actions we can see the following picture under model working area:

3. In order to sort data by the Corporate Experts names you need to right-click the cell where EMP_NAME column and the Sort row intersect, and choose the sorting type:

4. The EMP_NAME field will be displayed twice in the results of this given model, since it corresponds the “*” field. To avoid this problem you need to right-click the cell where the EMP_NAME column and Show row intersect and clear the Show option.

5. Click the Generate Query button to generate and see the SQL code. Generated request will be located in the Content field on the Query tab.

6. You can view results by clicking the Run Query button. You will get a table with a list of all the Corporate Experts and their data including service data.

7. To save the request model switch to the Query tab, specify the Query Name and Description (optionally) and click OK button.

Note: You can export or print these data from the Custom Queries page of the Server Administrator Corporate Settings (using Export Data and Print Data buttons) (the query should be saved as described in the previous paragraph).

Example 2

Query: Get a list of all the projects with all their data sorted by experts names with notification of the client’s name, Projetex manager name and client’s currency name.

1. Drag&drop PROJECTS, CLIENTS, CURR and AIT$USERS tables to the working area. In the PROJECTS table select “*” field (to display all table fields); select also  CLIENT_NAME field in the CLIENTS table, CURR_NAME field in the CURR table and AIT$USER_REALNAME field in the AIT$USERS table.

2. In order to get the name of the client the project is assigned to, you need to set connection between the PROJECTS and the CLIENTS table by unique field CLIENT_ID: click and hold left mouse button on the CLIENT_ID field in the PROJECTS field, drag it to the CLIENT_ID field in the CLIENTS table, and release the button. As a result you get the following:

Similarly you need to connect the tables CLIENTS and CURR by unique CURR_ID field, PROJ_PM field of the PROJECTS table and AIT$USER_ID field of the AIT$USERS table.

3. After the request model was created click the Generate Query button. SQL code of the request will be generated and displayed in the Content field on the Query tab.

4. You can view the results clicking the Run Query button: you get a table with a list of all the projects and their data including service data.

5. To save the request model switch to the Query tab, specify the Query Name and Description (optionally) and click OK button.

Note: You can export or print these data from the Custom Queries page of the Server Administrator Corporate Settings (using Export Data and Print Data buttons) (the query should be saved as described in the previous paragraph).

Example 3 (using functions)

Query: Get statistics on clients by countries (i.e. the number of clients from each country)

1. Drag&drop the tables CLIENTS and COUNTRIES to the working area. In the CLIENTS table check the COUN_ID field, and also COUN_NAME field in the COUNTRIES table.

2. Set the connection between the tables CLIENTS and COUNTRIES by unique field COUN_ID: click and hold left mouse button on the COUN _ID field in the CLIENTS field, drag it to the COUN _ID field in the COUNTRIES table, and release the button. As a result you get the following:

3. Now you need to specify the selection conditions. Set the necessary sorting order: number of countries (in the CLIENTS table) – descending, names of the countries – ascending). For the COUN_ID field choose the Count function, and also set grouping by country name:

4. After the request model was created, click Generate Query button. SQL code of the request will be generated and displayed in the Content field on the Query tab.

5. You can view the results by clicking the Run Query button. You will get the table with counties rating according to the number of your clients from each country. For example:

6. To save the request model switch to the Query tab, specify the Query Name and Description (optionally) and click OK button.

Note: You can export or print these data from the Custom Queries page of the Server Administrator Corporate Settings (using Export Data and Print Data buttons) (the query should be saved as described in the previous paragraph).