tologo Custom Queries

Top  Previous  Next

SQL queries can be made to TO3000, Version 9.0 database to extract and export required data. This feature recommended for  users with basic knowledge of SQL.

Structured Query Language (SQL) is a language of structured requests. It is intended for working with relational databases, which constitute the sets of interrelated data, stored in tables.

Currently SQL is a part of a large number of programs, executed on various types of computers. "Owing to its elegance and machine independence, as well as to the industrial leaders support in relational base technology, SQL was acknowledged the standard language and will keep this position in the foreseeable future."[2000, Mastering SQL, Martin Grubber]

 

_bm2

 

Creating a query

User queries are built and managed with the help of Custom Queries section of Advanced Settings window of .

1. Beginning creating a query

Click the New button and the Query Builder window shown below appears. Enter the name of your query in the Query Name field, and (optionally) description of the new query in the Description field.

query_tab_of_query_builder_window

 

2. Beginning creating the model of the query

Switch to the Model tab:

model_tab_of_query_builder_window

 

3. Selecting database tables

Locate the required tables on the list to the right (in this case – CINVOICES table) and drag them to the gray field of the Model tab of Query Builder window.

model_tab_of_query_builder_window_dnd

 

4. Selecting fields to be added to query

Select two fields, (in this case CLIENT_ID and CINV_TOTAL_B — client ID and sum of the invoice in basic currency) by clicking near the names of this fields in the table windows. These fields will appear in the lower area, which represents the list of selected fields.

model_tab_of_query_builder_window_fields_selected

 

5. Assigning functions

Right-click on the cell where fields CLIENT_ID (the selected field) and GROUP (SQL section Group by) intersect and select the Group option. Similarly in the cell of intersecting CINV_TOTAL_B and Function fields we select the Sum option:

model_tab_of_query_builder_window_context

6. Generating query

Click the Generate Query button.

generate_query_button

Newly built SQL-query will appear.

query_tab_of_query_builder_window_quuery_ready

Start executing the query (clicking Run Query button from the toolbar in the upper part of the window) and you will see the results.

data_tab_of_query_builder_window_results

_bm3 Note: Although for most basic queries you are not required to write the SQL code of the query manually, any additional code can be added by SQL-versed users to fully customize their queries. For example, the following strings can be added to this particular query:

SELECT FIRST 10 c4.client_id, SUM(c4.cinv_total_b),

(select client_name from clients where client_id = c4.client_id)

FROM cinvoices c4

GROUP BY c4.client_id

ORDER BY 2 DESC

In simple words it will mean the following: To select first ten clients (first 10 client_id) by counting the sum of invoices and display them in the order of sum reduction (ORDER BY 2 DESC).

Click Run Query button once again to see the new result.

_bm3 Note: If you click Generate SQL after editing the query manually the query will be rebuilt and the results of your work will be lost.

 

7. Saving newly created query

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

query_tab_of_query_builder_window

 

_bm2

 

See also:

Custom Reports