Skip to end of metadata
Go to start of metadata

You are viewing an old version of this page. View the current version.

Compare with Current View Page History

« Previous Version 2 Next »

Our Visual SQL Query Builder allows you to visually and visually and quickly connect, build, and test your SQL queries before using them in your project. Connect, create, and test queries visually in the same design environment - no need to install or open additional software. Visually select databases, tables, and columns. Visually create relationships (joins) between entities.

Overview

A query is a request for data or information from a database table or a combination of tables. This data can be generated as results returned by the Structured Query Language (SQL) or as pictorial trend analyses (charts or complex results, for example) of data mining tools.

Several different query languages can be used to perform a variety of simple to complex database queries.

Most database administrators are familiar with SQL, as it is the best known and widely used query language.

A query can be executed on a project through a few different methods. Below you will find them listed with a brief description.

Method 1: WhereCondition

The first method you can use is the WhereCondition. In this case, the data query will be performed in a table, created in Edit>Datasets> Tables. Because the table is already selected, you only need to provide the condition. You should also run the SelectCommand to update the query.

Method 2: Query

The second method is similar to the first, but uses a query, created in Edit> Datasets> Queries and linked to a provider (see image below).

To run the query, you need to select one of the tables in the database and the condition by which you want to filter the data. You need to run the SelectCommand to update the query.

@Dataset . Query . Query1 . Sql Statement = ” s e l e c t ? from Table1 where
UTCTimestamp  Ticks>=”  +  StartTime . Utc Ticks  +  ”   and   UTCTimestamp  Ticks<=”  +
EndTime . Utc Ticks + ”” ;
@Tag . TableTag = @Dataset . Query . Query1 . SelectCommand () ;

Method 3: Different location

This alternative uses the same statement shown above, but in a different location. Instead of recording it in a task/class or CodeBehind, it can be placed directly into Edit> Datasets> Queries> SqlStatement Column.

Method 4: SQL Query Builder

The last option is to use a feature called SQL Query Builder, found in Edit> Datasets. It is a graphical interface that facilitates the creation of SQL Statements from a specific provider. This method is not as common as the others, so more details about its functionalities will be explained below.

How to use SQL Query Builder

Loading data

Before you begin, you need to make sure that your databases and providers are configured correctly in the project. The first thing you need to do is load the data into Query Builder. To do this, open the app and click the Connect in the upper left corner.

A pop-up will appear with a combobox containing various types of providers. Select the one you'll work with. In this example, we will set up a connection to a database SQLite.

Now, we need to configure the path to the database. The path is the same as seen below in the DataSource field of the connection string.

If you have written the correct path, you should be able to see all available tables and their elements in the right corner. Double-click one of the tables to load their elements into the Sub Query Structure.

Properties

A Properties button is located in the upper-left corner. When you select it, a pop-up will open containing the customizable properties of Query Builder. The image below shows all properties that are available for customization.

Creating claims

After the selected table is loaded into the subquery structure, a statement is initialized at the bottom of the page.

You can filter individual columns from the table by selecting specific check boxes. When you do this, the system will filter the results of the table based on your selection. If you do not check any check boxes, the table has no filters.

Columns allow you to add conditions that filter the values in the table.

The column options are:

  • Visible: removes the entire column from the query results

  • Expression: the name of the original column

  • Column name: Give a table or column of a table a temporary name. The Aliases are often used to make column names more readable. One alias exists only during the consultation.

  • Classification type: sorts results in ascending or descending order

  • Sort order: sort the order of the columns in the results

  • Add: the values of multiple rows are grouped as input in certain criteria to form a single value of more significant meaning. E.g.: Average, Count, Sum.

  • Grouping: Group column elements. Allow the creation of filter conditions for groups

  • Criteria: criteria for the selection condition, for example: =,>, <,! =

  • Or: Even if the criteria

Claim example

To better illustrate the query builder feature, let's create an example that assumes the following requirements for the outcome of our query:

  • Only UTCTimestamp Ticks and HistoricalTag columns are required

  • All column names need to be easily understandable

  • The elements will be sorted in ascending order

  • We only want the HistoricaTag values between 10 and 35

Fill in the columns with these requirements as seen in the image below.

If everything was filled in correctly, the final SQL statement generated by Query Builder should be:

Select Table1 . Historical Tag As TagValue , Table1 . UTCTimestamp Ticks As Date
From Table1
Where Table1 . Historical Tag = Table1 . Historical Tag > 10 And Table1 . Historical Tag <= 35
Order By TagValue , Date

@On this page:

  • No labels