I. Overview
1. Function
Overview of dataset functions can query data from dataset，but sometimes users want to query data from database directly，instead of having to define a dataset and then query the data. At this point we can use SQL function.
2. Explanation
| Grammar | SQL(connectionName,sql,columnIndex,rowIndex) | The data returned is the element corresponding to the rowIndex row of the columnIndex in the table of the SQL statement obtained from the connectionName database. |
|---|---|---|
| Parameter 1 | connectionName | Data connection name, in string form, need to use quotation marks such as "FRDemo"; |
| Parameter 2 | sql | SQL statements, string forms, parameters, conditions, etc. can be spliced and implemented here; |
| Parameter 3 | columnIndex | column number, integer; |
| Parameter 4 | rowIndex | row number, integer; |
Note：The row number can be omitted, so that the return value is the data column.
II. Get specified content without parameters in database
Sample data: STSCORE data table in the built-in database FRDemo.
Take the data value of the third row and the third column from the STSCORE table in the built-in database "FRDemo".
From the table STSCORE, you can see that the value of row 3 and column 3 is Alex, as shown in the following figure：
Now if we want to display the data value directly in the cell of the report: Alex, instead of first defining a data set and then fetching the data, using the sql() formula, you only need to enter in the cell：= sql("FRDemo","SELECT * FROM STSCORE",3,3), you can see the Alex value in the preview, as shown follow：
III. Get specified content with parameters in database
Sample data: STSCORE data table in the built-in database FRDemo.
1. SQL parameters are normal parameters
Need to take out all the values of the 3rd column whose class is Class1.
Enter in the cell： =sql("FRDemo","SELECT * FROM STSCORE where CLASSNO = 'Class1' ",3), display the effect (all values in column 3 where the class is Class1), as shown follow：
Formula Explanation：
| Formula | Explanation |
|---|---|
| "FRDemo" | Data Connection Name |
| "SELECT * FROM STSCORE where CLASSNO = 'Class1' " | SQL statement; query data whose CLASSNO is Class1 |
| 3 | Column serial number, the data of the third column |
If you need to display a specific value, such as displaying Jonny (that is, the value of the third column and the fourth row of the class Class1), the writing method is as follows：
=sql("FRDemo","SELECT * FROM STSCORE where CLASSNO = 'Class1' ",3,4)
2. SQL parameters are variables
If the parameter value is a variable, such as a report parameter or a cell, it is written as follows：
=sql("FRDemo","SELECT * FROM STSCORE where CLASSNO = '"+$class+"' ",3,4) OR
=sql("FRDemo","SELECT * FROM STSCORE where CLASSNO = '"+A1+"' ",3,4)
For example, you want the filter widget to select different classes and display the names of all students in different classes.
First set the template parameter "class", then enter the formula in the cell：
=sql("FRDemo","SELECT * FROM STSCORE where CLASSNO = '"+$class+"' ",3)，as shown follow：
Effect as shown follow：
If the passed parameter is to get the value of the current cell, that is, when using $$$ as the parameter, the string type also needs to be spliced with single quotes, for example：
=sql("FRDemo","SELECT * FROM STSCORE where CLASSNO = '"+$$$+"' ",3,4)
Note : If there are multiple parameters or cell values, the SQL function is written as follows：
=sql("FRDemo","SELECT * FROM STSCORE where CLASSNO in ('"+$class+"') and COURSE in ('"+$COURSE+"') ",3,4)
Note : The delimiter of the return value of the class parameter needs to be ','. For details, please refer to the drop-down checkbox parameter linkage.
3. SQL parameters are variables and need to be concatenated
In SQL, you can also use the IF function to judge and splice template parameters. For example, if you want to select all student names when the parameter class is empty, you can enter the formula：
=sql("FRDemo","SELECT * FROM STSCORE where 1=1 "+if(len(class)== 0,"","and CLASSNO = '"+class+"'"),3)
Formula Explanation：
| Formula | Explanation |
|---|---|
| "SELECT * FROM STSCORE where 1=1" | Surround SQL statements with quotes as strings |
| +if(len(class)== 0,"","and CLASSNO = '"+class+"'" | The "+" here refers to the string concatenation symbol Concatenate the previous SQL statement with "+" When the parameter "class" is empty, the query statement is equivalent to: SELECT * FROM STSCORE When the parameter "class" is not empty, the query statement is equivalent to: SELECT * FROM STSCORE WHERE 1=1 and CLASSNO ='"+$class+"' |
| sql("FRDemo","SELECT * FROM STSCORE where 1=1 "+if(len(class)== 0,"","and CLASSNO = '"+class+"'"),3) | When the parameter "class" is null, it is equivalent to: sql("FRDemo","SELECT * FROM STSCORE",3) When the parameter "class" is not empty, the query statement is equivalent to: sql("FRDemo","SELECT * FROM STSCORE WHERE 1=1 and CLASSNO ='"+$class+"'",3) |
If the parameter is a fuzzy query in SQL, the following formula can be used：
=sql("FRDemo","SELECT * FROM STSCORE where CLASSNO like '%"+$class+"%' ",3,4)
