Tariscope 4.6. User's guide
Chapter 2. Tariscope application
2.2. Working with views for calls
2.2.13 Edit SQL filter
To use this option, a user should know the SQL and list of fields of the Tariscope database and their purpose.
This is the most powerful tool in Tariscope for creating the filtering options. To use it, click on the Edit SQL filter icon on the toolbar. The SQL-Filter window appears, an example of which is shown in Figure 2.2.13.1.
Figure 2.2.13.1
If you did not apply filtering options before, the window will be empty. Otherwise, it is displayed filtering options which are used in SQL query after the WHERE operator. You can enter a SQL query or change existing filter conditions. To apply the filter conditions, click on the Save button.
A list of possible operators which you can use in the SQL-Filter window is shown in Table 2.2.13.1
Table 2.2.13.1
| 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 equal | 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<= '2017-06-20' |
| > | 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 '%Іndia%' |
| 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 |
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. In the case you should enter the following string:
(CallType = 4 OR CallType = 5) AND (CallDuration > '00:10:00' OR Cost > 5)
In the expression we used the following fields of the viCalls view contained in the Tariscope database:
CallType. Call type. The value of 4 is used for long-distance calls. The value of 5 is used for international calls.
CallDucation. Call duration with format ‘hh:mm:ss’.
Cost. Cost of a call.
There are 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)
