Custom Queries

SQL queries can be written toto extract and export required data out of the TO3000 3D database. This feature is recommended for users with basic knowledge of SQL.

The Structured Query Language (SQL) is a language of structured requests. It is intended for work 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]

Creating a query

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

1. Beginning creating a query

Click the New button and the Query Builder window shown below will appear. 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 – the CINVOICES table) and drag them to the gray field of the Model tab of the 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 — the client ID and sum of the invoice in the base currency) by clicking near the names of the 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 the fields CLIENT_ID (the selected field) and GROUP (SQL section Group by) intersect and select the Group option. Similarly in the cell of the 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

The newly built SQL-query will appear.

query_tab_of_query_builder_window_quuery_ready

Start executing the query (clicking the 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

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 the 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 the Run Query button once again to see the new result.

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 the newly created query

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

query_tab_of_query_builder_window