Select From Data Table Variable Step

Brief description

Step allows SQL queries to be executed on variables that are defined as a Datatable type.

The result is written to a new variable of type Datatable. 

Step Information

Step name (Not required) – the custom name of the process step that labels the editor’s process step box

Input parameters

• SQL Select Query (Required)- SQL query that is executed on DataTable variable

Output parameters

• Destination DataTable (Required) - existing variable name of DataTable data type that contains the resulting value
• Row Count (Required) - existing or new variable name of data type “Integer” containing the resulting data table variable count of columns if the SQL query statement implies resulting value
• Column Count (Required)- existing or new variable name of data type “Integer” containing the resulting data table variable count of columns if the SQL query statement implies resulting value

Additional comments

Each step that results in a variable of type Datatable can be used as a data source for query execution.

Some values ​​that can be saved to Datatable type:

- The result of the query to the database

- Table from the website (Selenium steps)

- Excel Sheet

- ...

In order not to write loops and manually pair the data through many steps, Query Variables Step can help us, which solves everything in one SQL query.

Step first:

- query (SQL Expression)

- variable in which the result will be written (type datatable)

The name of the datatable variable is the name of the table in the query, while the name of the datatable column is the name of the column in SQL.

Eg. If we have a variable "movies" of type datatable, we can query it:

select * from movies

but the result will return the same values, it will only save them in a new variable, defined in the step.

Filtering is done with the "where" keyword, so if we have a "movies" variable stored in the datatable type:

Inquiry:

Select * from movies where id = '1'

Will return a new datatable:

We can also join, so if we have actors in a variable of type datatable named "actors":

We can join the data by querying:

select actor, name as movie_name from movies m join actors a on m.id = a.movie_id

We get:

If there is a space or uppercase and lowercase letter in the column or table name, a backtick is used.

Eg. Select `Titles` from` my movies` where id = 5

In the background, step creates an inmemory sqlite database and executes queries on it. Sqlite has no data types in the table. It is good if we do join and we know that we join by columns of type integer to make a cast, so it is better to write for the above query of movies:

select actor, name as movie_name from movies m join actors a on cast (m.id as integer) = cast(a.movie_id as integer)

To ensure consistency of data if we join them from different sources.

The entire syntax is a Sqlite syntax that allows the vast majority of standard sql syntax and more detailed restrictions and instructions can be found at link.

It is important to emphasize that Query Variables Step is for select. More specific instructions for select can be found at link.