Creating Selection Views

A Selection view executes the relational algebra operations of selection (filtering) and projection on an input view. This view can belong to a different database.

To create a selection view, click Selection on the File > New menu or right-click on the Elements Tree and click Selection on the New menu.

The Tool will open the “Selection view” dialog and it will add the view that is currently selected in the Elements Tree. To change the input view, click the button image0 to remove the current view from the “Model” tab and drag another view from the Elements Tree.

In our example, we will drag the view incidents_sales created in the section Creating Join Views.

The “Selection view” dialog has five tabs:

  1. Model: tab where you have to drag the view that will be the source of the new view. You can drag a view from another database.

    In this tab, you can add “View parameters”. See more about this in the section Parameters of Derived Views.

  2. Where Conditions: tab that allows you to add WHERE conditions to the definition of the view.

    If you add WHERE conditions, you have to select one of the WITH CHECK OPTION clause options to decide if you want to force every row that is inserted or updated, to conform to the definition of this view (see section Use of WITH CHECK OPTION of the VQL Guide). The available options are:

    1. None: nothing is checked when a client executes INSERT operations on this view.
    2. Local: the selection condition of this view is checked when a row is inserted or updated. It does not check the conditions of the lower-level views (the views that participate in the definition of this view).
    3. Cascade: the projection conditions of this view and the lower-level views are checked when a row is inserted or updated.

    If the “Automatic simplification of queries” is enabled, when executing an INSERT/UPDATE/DELETE query over a derived view, the Server assumes that this view was created with the option “WITH CHECK OPTION”. As a result, the Server checks that the data inserted/updated/deleted meets the WHERE condition of the definition of the view.

    To check if this option is enabled, click “Queries optimization” on the menu Administration > Server configuration.

  3. Group By: tab that allows you to add GROUP BY fields to the view.

  1. Output: tab that allows you to configure the output of the view. That is, renaming the view and its fields, add derived attributes, define the primary key of the view, etc.
  2. Metadata: tab that allows you to define the folder where the new view will be stored and provide a description for the new view.

The tabs Where Conditions, Group By, Output and Metadata work in the same way as in the Union view dialog. The section Creating Union Views explains in more detail how to use them.

You will now create two views for our example:

  • Right-click on the view incidents_sales in the Elements Tree and click on Selection, on the menu File > New. You can see that the Tool has added this view to the Model tab.
  • In the Where Conditions tab, add the condition revenue>600 to obtain data only of those clients with an average monthly volume of sales that exceeds 600 euros.
  • In the Output tab, rename the view to pref_clients_inc_sales.

After this, click Save to create the view. Then, the Tool will display the schema of the new view.

Now, you have to create the second view, which has a GROUP BY field:

  • Right-click on the view pref_clients_inc_sales in the Elements Tree and click Select, on the File > New menu.
  • In the Group By tab, do the following:
    • Select the Use group by check box.
    • Select the taxId and revenue fields and click Add >>.
  • In the Output tab, do the following:
    • Rename the view to inc_grouped_by_pref_clients.
    • Click New Aggr. expression. The name of the new field is num_incidents and the expression is COUNT(*).
  • Click Save.
Creating the selection view inc_grouped_by_pref_clients ("Group By" tab)

Creating the selection view inc_grouped_by_pref_clients (“Group By” tab)

Creating Conditions with the Compound Values Editor

All the dialogs to create and edit derived views (join, union, minus, intersect, flatten and selection) have a Where Conditions tab to add WHERE conditions to the definition of the view. When using the Simple condition mode of this tab, you have two options to create constants of compound types (register or array):

  1. Write the value directly in VQL syntax. E.g. { ROW( 'B78596011' ), ROW( 'B78596012' ) }

    See section Conditions with Compound Values of the VQL Guide for more details about this syntax.

  2. Or, use the Compound values editor. To open it, click on image1 beside the right operand (only available for register or array fields).

As an example, we explain how to create a new Selection view that has a WHERE condition with a compound value. But first, we need to create a new base view:

  1. Open the Web Service data source sales (created in the section Importing SOAP Web Service Sources) and click Create base view.
  2. Click Create base view beside the operation getSumRevenueByTaxIds.
  3. Rename the new base view to RevenueSum.
  4. Rename its input attribute to clients and the return one, to totalrevenue.
  5. The clients attribute is an array of registers of the type getsumrevenuebyids_in0. Virtual DataPort generates this type automatically when it creates the view.
  6. Each element in this array will be a register of the type getsumrevenuebyids_in0_string, which is also automatically generated by Virtual DataPort.
  7. Rename the element of the clients array to taxId.
  8. Click Save (image2) to create the base view.

Now, we will create a selection view over the base view RevenueSum, to obtain the sum of the revenue of the clients with tax ids B78596011 and B78596012. The constant operand of the selection condition will be created using the “Compound values editor”.

Follow these steps:

  1. Right-click on the view RevenueSum in the Elements Tree and click on Selection on the menu New. The view RevenueSum has been added to the “Model” tab.
  2. Click the tab Where Condition.
  3. Click on image4 to add a condition.
  4. Select the field CLIENTS and the operator =.
  5. Click image1 to open the Compound Values editor.
  6. Click twice on image5 beside “Value” to create two elements of the type getSumRevenuebyTaxIds_in0_string.
  7. Click on the new elements to expand them and edit the values of each array element.
  8. Enter the value B78596011 in the field taxId of the first register and B78596012 in the same field of the second register (like in the figure below)
  9. Click Ok to close the editor.
  10. Click Save (image2) to create the selection view.
Creating a value of type getSumRevenuebyTaxIds_IN0

Creating a value of type getSumRevenuebyTaxIds_IN0

Parameters of Derived Views

There are scenarios where you may need to create a derived view with a Where condition, use Group by and you do not want the fields involved in the condition to be Group by fields. In this scenario, the Where condition has to be static and cannot be changed at runtime.

For example, let us say that we have two views:

  1. A base view CLIENT with these fields: name, income and state.

  2. And a view WEALTHY_CLIENT_BY_STATE defined as:

    CREATE VIEW WEALTHY_CLIENT_BY_STATE AS
    SELECT state, COUNT(*)
    FROM client
    WHERE income > 1000000
    GROUP BY state
    

There is a limitation in the second view: the limit of income to consider a client wealthy is static. Therefore, we have to know this limit before creating the view. If we wanted to change this limit at runtime, we could remove the WHERE condition and add the field income to the GROUP BY fields. But then, we would have to add this field to the GROUP BY clause and we might not want to do that.

To avoid this problem, you can add a “View parameter” to the view. View parameters are a special type of fields:

  • They are added to the output schema of the view
  • You can use them in the Where condition of the derived view, as any other field

At runtime, to set the value of a parameter you have to add its value in the WHERE clause of the query as if it was a regular field (i.e. VIEW_PARAMETER = <value>). By doing this, you are setting the value of the parameter.

Note that when you use parameters in the WHERE clause, the Server treats them differently because it does not use their value to filter the output. Only to set the value of the parameters.

Following our previous example, you can modify the derived view WEALTHY_CLIENT_BY_STATE to add a view parameter that allows you to modify at runtime the limit of income. To do this, edit the view and do the following:

  • Click on the Model tab and click image1 beside “View parameters”. In this dialog, click Add new parameter to add the new view parameter (see Parameters of Derived Views).

    Set the name of the parameter to wealthy_client_income_limit, select the type long and set the default value of the parameter to 1000000.

  • Click on Where Conditions and set the condition income > wealthy_client_income_limit.

Adding a view parameter

Adding a view parameter

The view will have a new output field wealthy_client_income_limit, which you can use to change the condition at runtime. For example,

SELECT *
FROM WEALTHY_CLIENT_BY_STATE
WHERE wealthy_client_income_limit = 250000

Will take into account clients with an income greater than 250,000.

As the parameter has a default value, if you execute

SELECT *
FROM WEALTHY_CLIENT_BY_STATE

The query will take into account clients with an income greater than 1,000,000.

Important

Do not assign the result of a subquery to a view parameter. The query will not work as expected or return an error depending on if the parameter has a default value or not.

For example, following the example above, let us say that you execute the following:

SELECT *
FROM WEALTHY_CLIENT_BY_STATE
WHERE wealthy_client_income_limit = (
   SELECT MAX(income_limit) FROM state_limit WHERE state = 'CA'
)

In this case, the result of the subquery (SELECT MAX(income_limit) FROM…) will not be assigned to the parameter wealthy_client_income_limit so the query will return the accounts with an income greater than 1,000,000, which is the default value of the parameter.

If the parameter did not have a default value, the query would return an error immediately.