Java report tool FineReport's SQL edit box syntax introduction, finereportsql

Java report tool FineReport's SQL edit box syntax introduction, finereportsql
Thank you for your support. Here we will continue to share some of the syntaxes on the SQL edit box, because the dataset definition panel is also one of the most common modules in FineReport reports.

1. I understand the execution process.

In fact, a string is generated. FineReport transfers the string to the report for execution through the set data connection. In this process, the report should first Replace the report rule with a string recognized by everyone. After the corresponding database is executed, the value will be returned and an error message may be returned, it is also possible to return a dataset. This error message should be sent from the corresponding database. Therefore, different databases may execute the same SQL statement using different SQL syntaxes. For example, if select 'sdf 'is normally displayed in access and sqlserver, an error is thrown in oracle. Select version (), show status, or show tables can be normally executed in the mysql dataset definition.

The and or in SQL must be different from the formula. Someone may use & As and in the formula. Or | in the formula, it is a string connector in oracle.

 

2. SQL call Formula

The report interacts with SQL statements through formulas and parameters.

Put the formula in the frame $. Therefore, $ {"select * from t1"} is equivalent to select * from t1.

The difficulty in using formulas in a dataset is that splicing is cumbersome.

I think the first example that many people come into contact with is to return all SQL statements with null parameters in the help document: SELECT * FROM order where 1 = 1 $ {if (len (area) = 0, "", "and subject region = '" + area + "'") }$ {if (len (province) = 0 ,"", "and goods owner province = '" + province + "'")}

The following is an example of calling format in FR:

Select $ {"'" + format (p1, "yyyy-MM-dd") + "'"} from aa

If the single quotation marks before and after the format function are missing, an error is returned. In fact, some methods can be used to simplify processing. For example, to declare another variable p2, the definition of this variable is format (p1, "yyyy-MM-dd "), at this time, the preceding SQL statement becomes select '$ {p2}' from aa [of course, if no quotation marks are added, p2 will be parsed into a field name, this is the implementation of dynamic fields]

For stored procedures, the dataset definition actually supports call and exec call methods.

 

3. calling SQL functions in SQL

As mentioned above, SQL statements are actually strings. In fact, the database can call strings as an SQL statement. The SQL function is used as an example:

= SQL ("FRDemo", SQL ("FRDemo", "selecttesttext from test where id = 2", 1, 1), 1, 1)

The testtext field of the second data in the test table is the from sales volume in the select top 1 region.

This string can be used in the SQL formula for SQL statement execution.

Data Set definition can be written in this way $ {SQL ("FRDemo", "select testtext from test where id = 2 )}