Editing the SQL Statements of a Query

For a query built on tables, views, and synonyms from one JDBC connection only, its SQL statements can be edited.

To edit the SQL statements of a query, in the Query Editor click Menu > View > Edit SQL, the SQL dialog is then displayed showing the SQL statements used to execute the query like below.

SQL statements

You can edit the query statements in the text area as follows:

To check whether it can be parsed back to the structured PSQL query correctly, click Parse. A message box will be displayed to show you the result. If the result cannot be parsed you should copy the SQL statements into a file and import the file as an imported SQL. If you accept the SQL that cannot be parsed by JReport, the Query Editor will be deactivated and all future changes to the SQL will have to be made by this SQL dialog.
To see whether it can be successfully processed by the database, click Execute.
To accept the changes you have made, click OK. JReport Designer will then parse and execute the query statements and close the Query Editor.

Notes:

If there are parameters in this query, their default values will be used to construct a valid statement.
If JReport Designer fails in parsing or restoring the query statements, a message box will be displayed asking you whether to apply the changes anyway. If you choose yes, a statement string will then be recorded in the PSQL query. However, after that, the visual structured elements may be not tally with the statement string and all user interface functions will be disabled.

Customized SQL mode

When creating a query using the Query Editor, JReport allows you to copy and paste your own SQL statements directly in the SQL dialog as explained above. However, because JReport query parser recognizes a limited set of the SQL 92 standard, sometimes JReport is not able to parse the customized SQL statements. For example, functions such as aggregations using the GROUP BY clause are not supported. In this case, a warning message is displayed after you click the OK button in the SQL dialog asking whether to continue using the customized SQL statements. If you choose Yes, the customized SQL mode is enabled, in which all the functions in the Query Editor will be disabled, and you can only edit the query by modifying the SQL statements in the SQL dialog. You should guarantee the correctness of the customized SQL statements since JReport will not parse it although you can check it by using Execute and passing it to the database to check.

While using the customized SQL mode, you can see that the Menu > Query > Customized SQL Mode option of the Query Editor is enabled and checked. By unchecking this option, you can go back to use functions of the Query Editor, in which case the customized SQL statements will be lost and the SQL statements generated by JReport will be applied instead.

Therefore, if you want to use customized SQL, it is much better to copy your SQL to a text file and import it using the Import SQL feature.

Previous Page Next Page