Chapter 3 The Tariscope program
3.5 Working with views
3.5.10 SQL queries
The most powerful tool in Tariscope for creating the filtering conditions is SQL queries. To create them in the Tariscope program, use the SQL filter mode which is accessible from the Filter menu or by clicking the SQL filter icon on the toolbar. After selecting this option, the View filer window appears, an example of which is shown in Figure 3.5.29.
Figure 3.5.29
The SQL filter allows you to set an arbitrarily complex filtering settings for any fields of the call database. We recommend using this filtering method, if you cannot create a desired filter using the advanced filter, or for you is easier to write a SQL query, rather than to select the desired parameters in a graphical form. As a rule, the creating of SQL queries requires the knowledge of SQL language. But a new interface of the window allows building a huge number of such queries almost without any knowledge of the SQL language.
The View filter window (Figure 3.5.29) is divided on two parts. The top part provides a creation of a SQL query by selecting a desired field and operator and typing a desired value.
The resulting partition of a SQL query that is contained after the WHERE operator is displayed in the bottom part (the Query box) of the window. If you well know the SQL queries, you can write a SQL query at once in the bottom part of the window.
If a view does not contain any filter conditions, the View filter window will be as shown in Figure 3.5.30.
Figure 3.5.30
To add a filter condition, click on the “+” button in the window. A new row appears in the window as shown in Figure 3.5.31.
Figure 3.5.31
The row consists of three parts. In left part a field of the call database is selected. The middle part provides a selection of an operator. In the right part it should type a value for the filter condition.
Double-click on the left part of the row. The list of the fields appears. Select a desired field. A list of the fields and their descriptions, see in Table 3.2.
Click on the middle part of the row. The menu appears that contains possible operators for a SQL query. Select a desired operator. A list of possible operators is shown in Table 3.7.
Table 3.7
| Operator | Function | Result of query | Example of using |
| = | Equals | Returns all records where value of field is equal to a particular set of characters entered after the operator | Calls from subscriber with the direct number '347': [Originator] = '347' |
| <> | Does not equals | Returns all records where value of field is not equal to a particular set of characters entered after the operator | Calls with cost is not equal 0: [Cost] <> 0 |
| < | Is less than | Returns all records where value of field is less than value entered after operator | Calls with duration less than 10 seconds: [CallSeconds]<10 |
| <= | Is less than or equal to | Returns all records where value of field is less than or equal to the value entered after operator | Calls with date 09.01.2010 or earlier: [CallDate] <= '2010-1-9' |
| > | Is greater than | Returns all records where value of field is more than value entered after operator | Calls with cost more than 5 euro: [Cost] > 5 |
| >= | Is greater than or equal to | Returns all records where value of field is more than or equal to the value entered after operator | Calls with cost is equal to 5 or more: [Cost] >= 5 |
| Like | Is like | Returns all records where values of fields are like to the entered pattern | All calls to USA: [ToTelephone] LIKE '%USA%' |
| Not Like | Is not like | Returns all records where values of fields are not like to the entered pattern | All calls except calls to the India: [ToTelephone] NOT LIKE '%India%' |
| In | Is any of | Records are filtered out, which correspond to elements of a given list | Long-distance, international calls: [CallType] IN (4, 5) |
| Not In | Is none of | Returns all records where the value is not equal to any values that the list contains. | Calls from PBXs except PBXs with the following identifiers 33,27: NOT ([PBXID] IN (33, 27)) |
| Between | Is between | Returns all records where values of the specific field are between two specific values | Calls for the period from 09.01.2006 to 31.01.2006: [CallDate] BETWEEN '2006-1-9' and '2006-1-31' |
| Not Between | In not between | Returns all records where values of the specific field aren’t between two specific values | Calls for the period except from 08.08.2016 to 17.08.2016: [CallDate] >= '20160808' AND [CallDate] <= '20160817' |
| Is null | Is blank | Returns all records where the field value is null. | Calls where the originator of calls is null: [Originator] IS NULL |
| Is not null | Is not blank | Returns all records where the field value is not null. | Calls where the originator of calls isn’t null: [Originator] IS NOT NULL |
Double-click on the right part of the row. Depending on the selected field, the list of the possible values appears or the box for entering a desired value appears. Select or type a desired value. The bottom part of the window will display the SQL query.
To add the next filter condition, click on the “+” button in the window. A new row appears. Repeat the actions described above.
The first and second conditions are linked by a logical operator. By default, the AND operator is used. To change it, click on it and in the appeared menu, select a desired operator.
If you wish you can directly write a SQL query in the bottom part of the window.
Consider the example of creating a query. For example, you wish to filter out all national (long-distance) or international calls and that are longer than 10 minutes or their cost is more than 5 euro.
To create this query in the View filter window, click on the And link. In the appeared menu, select the Add Group item. The new And link and ‘+’ button appears. Click on the second And link and, select Or. Double-click on the left part of the row and in the appeared menu, select the Call type item. In the middle part of the row, select the Equals operator. Double-click on the right part of the row and, select the National item. In the Query box, you can see the following string:
([СallType] = 4)
To add the filtering condition on international calls in the query, click on the “+” button near the Or link. A new row appears. Double-click on the left part of the row and, select the Call type item. Select the Equals operator. Double-click on the right part of the row and, select the International item. In the Query box, you can see the following string:
([CallType] = 4 OR [CallType] = 5)
To add the information about call duration, click on the “+” button near the And link. A new row appears. Double-click on the left part of the row and, select the Duration item. In the middle part of the row, select the Is greater than operator. Click on the enter value prompt and, type 00:10:00. In the Query box, you can see the following string:
([CallType] = 4 OR [CallType = 5]) AND CallDuration > '00:10:00'
On the next step we add information about cost of calls. Click on the “+” button near the And link. A new row appears. Double-click on the left part of the row and, select the Cost item. In the middle part of the row, select the Is greater than operator. Click on the prompt and, type 5. In the Query box, you can see the following string:
([CallType] = 4 OR [CallType] = 5) AND [CallDuration] > '00:10:00' AND [Cost] > 5
Add the brackets for the expression: [CallDuration] > '00:10:00' AND [Cost] > 5, and, change AND to OR. The query will be as shown below:
([CallType] = 4 OR [CallType] = 5) AND ([CallDuration] > '00:10:00' OR [Cost] > 5)
On the toolbar, click SQL Apply filter, and you will receive the required calls.
May be other options for creating the query. The correct query string, which is completely analogous to the above, would be:
((CallType = 4 OR CallType = 5) AND CallDuration > '00:10:00') OR ((CallType = 4 OR CallType = 5) AND Cost > 5)
or
CallType IN (4,5) AND (CallDuration > '00:10:00' OR Cost > 5)
The last query is the most compact. It uses the IN operator, which in this case means that the CallType field includes a list of: 4, which corresponds to national calls, and 5, which corresponds to international calls.
A user, who knows the SQL, has possibility to enter a query in the Query box.
To quickly clear all filter conditions in the View filter window, click on the Clear icon located on the toolbar.
If it is assumed that a query has been created in the View filter window can be used in the future, save it. To do this, open the View filter window and, click on the Save as icon located on the toolbar.
To select the previously saved filter, open the View filter window and, click on the Open icon located on the toolbar.
