Introduction to the syntax of the SQL edit box for the Web Report Tool Finereport

Thank you for your attention, here continue to share some syntax tips for the SQL edit box, because the dataset-defined panel is also one of the most commonly used modules in the Finereport report.

1. I understand the implementation process.

This is actually a string that is generated and Finereport is executed by passing the string to the report by setting the data connection. In this process, the report should be the report rules of the things, replaced by everyone knows the string, the corresponding database execution, will be feedback value, it is possible to return the wrong information, it is possible to return the data set. This error message should be given by the corresponding database. So the same SQL statements, different databases with different SQL syntax to execute may be different results. For example, select ' SDF ' is displayed normally in Access and SQL Server, and the Oracle is thrown wrong. Use Select version () or show status or show tables to execute normally in the MySQL dataset definition, others are not.

The AND and or in SQL is different from the formula, and one might use && in the formula. There's a formula inside or | | A connector that is a string in Oracle.

Calling formulas in 2.sql

Reports interact with SQL statements through formulas and parameters.

The formula is placed in frame ${}. So ${"select* from T1"} is actually equivalent to select *from t1.

The difficulty of using a formula in a data set is that it is more cumbersome to stitch together.

I think the first example that many people come into contact with is that the parameters on the Help document are empty to return all the SQL statements: SELECT *from order where 1=1${if (len (area) = = 0, "", "and shipper region = '" + areas + "'")} ${if (Le N (province) ==0, "", "and shipper province = '" + Province + "'")}

Here is an example of calling format in fr:

select${"'" +format (P1, "yyyy-mm-dd") + "'"} from AA

If you miss the single quotation mark before and after the format function, you get the wrong result. In fact, there are some ways to simplify the processing, such as declaring a variable P2, the definition of reference to this variable is format (P1, "Yyyy-mm-dd"), when the above SQL becomes the select ' ${p2} ' from AA ' Of course, if there is no quotation marks, then the P2 is parsed into a field name, which is the implementation of the dynamic field "

For stored procedures, the dataset definition is a place that supports call and exec calling methods.

Calling SQL functions in 3.sql

As I said earlier, the SQL statement is actually a string. In fact, you can invoke the string from the database as an SQL statement operation. This is going to use the SQL function, say the example:

=sql ("Frdemo", SQL ("Frdemo", "Selecttesttext from Test where id=2", 1, 1), 1, 1)

Test the Testtext field of the second data in this table is the string select top 1 region from sales

You can use this string in an SQL formula as an SQL statement.

DataSet definition It's OK to write that way. ${sql ("Frdemo", "Select Testtext from test where id = 2", 1, 1)}

Introduction to the syntax of the SQL edit box for the Web Report Tool Finereport