CHMlib Logo Leading Translation Management System for Translation Agencies, Top Translation Management Software for Freelance Translators, Time Tracking Software, Word Count, Character Count and Line Count Software, Computer Assisted Translation Tool for Easy Word Count, Clipboard Character Count Software, User-Friendly Translation Memory Software, Terminology Management Software, Terminology Management Server, Microsoft Glossary Software, Dictionary of Acronyms, Social Network for Translators and Translation Agencies, Acronyms and Abbreviations Search Engine, Russian Translation Lab, Ukrainian Translation Lab.
You are reading help file online using chmlib.com
CrisisHelper - First Aid in Tough Times of World Economic Crisis


_bm0 Building Queries (with examples)

Top  Previous  Next

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.

 

_bm2

 

exampleExample 1

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

1. Drag&drop EMPLOYEES table at the working area_bm250

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:

ex1

 

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:

ex2

 

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.

_bm3 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).

 

_bm2

 

exampleExample 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.

ex3

 

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:

connection

 

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.

_bm3 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).

 

_bm2

 

exampleExample 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.

ex4

 

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:

ex5
 

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:

ex6

 

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.

ex7

 

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:

ex8

 

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

_bm3 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).

 

 

 



You are reading help file online using chmlib.com

If you want your help file to be removed or added please send e-mail to chmlibcom@gmail.com
Partner sites: Logo Design, Simple Anti-Crisis Accounting Software, Voice Search for Web