Database

In Mitra, the SQL View is one of the three ways to load data into your components. This functionality allows you to create a query directly in SQL using the tables from your database, and the resulting query will feed the configured components.

2. Configuring the SQL View

To configure an SQL View, follow the steps below:

2.1. Query Area

This is the space where you will write your SQL query. As you build the query, you can use the data dictionary on the right side of the screen to view all available tables and columns, corresponding to the entities you created in the database.

2.2. Filter and Input Variable Support

The SQL View allows interaction with filters and inputs that the user adds to the interface. This functionality enables a dynamic filtering and search experience, respecting user interactions.

2.2.1. Filter Variables

Every filter configured on the screen can be accessed within the SQL query as a variable. These filters can be used to have your query filtered according to the user’s selection. The format for filter variables is :ID_ENTITYNAME.

Practical Example:

Suppose you want a table to filter data for executives selected by the user through a selector. If the user selects an executive, your query can be structured using the filter variable :ID_EXECUTIVE to filter the data for that specific executive.

SELECT ID, DESCR, EMAIL FROM CAD_1003
WHERE ID = :

This query would return only the data for the executive selected in the interface’s selector, enabling direct and flexible interaction between the user and the data.

2.2.2. Input Variables

Similarly, you can interact with inputs added to the interface. The values entered by users can be used in your queries and other database-related actions. The format for input variables is :INPUT_ID. Refer to the "Input" component documentation to better understand how it is configured.

Practical Example:

Let’s say you want to allow the user to search for a salesperson’s name in a list. By adding an input component for the user to type the salesperson’s name, that value can be used in the SQL query by applying the input variable :INPUT_2737 as shown below:

SELECT * FROM CAD_EXECUTIVE
WHERE DESCR LIKE '%' :INPUT_2737 '%'

Here, :INPUT_2737 is the ID of the input component, and the query will filter the results by the name entered by the user. If the user types "Maria", the query will return all records where the name contains "Maria".

2.3. Column Mapping

After executing your query, you will have the option to map specific columns to related records. For example, if your query returns executive data, you can mark the executive ID column so that Mitra understands that this column corresponds to an executive record. This facilitates future interactions, such as editing forms or detail modals, as the system will know which filter to apply based on this mapping.

With these steps completed, your SQL View will be configured and ready to populate the desired components.