SQL Query Dataset

SQL Query Dataset refers to a dataset that retrieves data by defining dataset conditions through custom SQL statements.

Create Dataset

Follow these steps to create a SQL Query Dataset.

1. On the dataset interface, click to create a new dataset and select SQL Query Dataset.

2. Select the data connection to be used for creating the dataset in the data connection section.

   Tip

   If the desired data connection is not found, you need to contact the data administrator to authorize the required data connection before use.

3. Write the SQL code in the editing area to define the dataset conditions. Click Execute to view the effect, and confirm that there are no issues before proceeding to the next step.

   Tip

   Each modification to the SQL statement requires clicking Execute. Only after successful execution can you proceed to the next step.

4. Enter the data structure page where you can set field aliases and types.

   Acceleration engine can be enabled, which will import the dataset into the acceleration engine.

5. After clicking Import, set the dataset name to complete the dataset creation.

Dynamic Macros

SQL Query Dataset supports dynamic macros, which can dynamically define dataset creation conditions, and the content of the dataset changes with the dynamic macros.

Dynamic Macro Definition

Dynamic macros start with {{!! and end with }}, with the middle code being SQL type, as shown in the figure.

Tip

1. The code in the dynamic macro must return a value of type String or number, and there can only be one field and one value, otherwise an exception will be thrown.
2. The processing order of SQL datasets is to first perform parameter and user attribute substitution, and then parse and execute the dynamic macros.

Dynamic Macro Example

Dynamic macros treat the incoming macro value as an expression for calculation, and then pass the calculated value as the macro value. This method can be used in SQL functions where expression calculation is not possible.

For example, in MySQL data sources, the limit function cannot perform expression calculations. For the requirement of retrieving two data each time select * from table1 limit n*2,2 , the limit cannot be achieved. In this case, dynamic macros can be used to achieve this.

When creating the dataset, dynamically change the starting position of the retrieval through parameter expressions, retrieving two data each time.

The effect is shown in the figure. The left table achieves limit n*2,2 through dynamic macros, retrieving two data each time. The right table achieves limit 0,2*n through dynamic macros, retrieving two data each time, with cumulative display. Both tables are dynamically controlled by the same parameter to retrieve content.