#### Predefined SQL files

For users who wish to write their own SQL statement, JReport enables them to put the SQL statement into a file and then load them from this file. Note that at present each SQL file can only contain one SQL statement.

Usually, you can save the file containing the SQL statement as a plain text file (.txt). The SQL statement here supports the SQL 92 standard. Although for different databases, it may vary. The basic statement is:

`SELECT...FROM...WHERE`

Nested query is also supported.

The following is an example of an SQL file:

    SELECT Catalog."Product Type Name", Catalog."Product Type ID",
    Catalog.Description, Products.Category,
    Products."Product ID", Products.Price, Products."Product Name",
    Customers.Region, Customers."Contact Position", Customers.Country,
    Customers."Customer ID", Customers.Address2, Customers.Address1,
    Customers."Contact Title", Customers.Phone, Customers."Contact Last Name",
    Customers.City, Customers.Fax, Customers."Contact First Name",
    Customers."Annual Sales", Customers."Customer Name", Customers."Postal Code",
    Orders."Order ID", Orders."Required Date", Orders."Customer ID",
    Orders."Shipping Cost", Orders."Ship Date", Orders."Order Date",
    Orders."Employee ID", Orders.Shipped, Orders."Payment Received",Orders."Ship Via"
    FROM Catalog, Products, Customers, "Orders Detail", Orders
    WHERE (Products."Product Type ID"=Catalog."Product Type ID")
    AND ("Orders Detail"."Product ID"=Products."Product ID")
    AND (Orders."Customer ID"=Customers."Customer ID")
    AND (Orders."Order ID"="Orders Detail"."Order ID")
    AND (( Customers.Country='USA' ))

The SQL files can work like queries in JReport, but there are two basic differences:

- A query is based on the tables/views added from a raw database, which means, if you have only set up a connection, but have not added any tables or views into the catalog, you will not be able to build a query. However, after you have set up a connection, even if you have not added any tables or views, you can still import an SQL file into a catalog, and then build reports on it.
- Queries built using the interactive query designer will work with other tables/views when you create or modify a working set. That is, if you design a report using interactively created queries, you can add additional objects from tables/views into the working set afterwards. However, when you design a report using an SQL file, you will only be able to use the contents already included in the SQL file. Due to this, you should make sure that you have included all the required fields in your SQL file.

In addition, JReport Designer provides you with the Data Manager which allows you to control the data retrieval of your SQL files that function as queries, including the number of rows to be displayed and the duration required for the retrieval. It can also keep access information from previous runs of an SQL file.

##### Adding SQL files to a catalog

After you have set up the JDBC connection and created some SQL files, you can then add them to a catalog. To do this:

1. In the Data tab of the Catalog Browser, right-click the Imported SQLs node of the JDBC connection, then click Add SQL on the shortcut menu to display the Select SQL File dialog.
2. Browse to the SQL file and click Open.
3. In the Input SQL Name dialog, enter a name for the SQL file in the SQL Name text box, then click OK to add the SQL file to the connection.

Notes:

- The format of an SQL file, such as comments, is remained when it is added into a JReport catalog.
- You can use parameters in the WHERE clause of an SQL file to filter the dataset. For example, in the above mentioned SQL file, if you want to use a parameter to return a result set in which the customer ID is greater than the parameter, then the WHERE clause would be like this:

  `WHERE (Products."Product Type ID"=Catalog."Product Type ID") AND ("Orders Detail"."Product ID"=Products."Product ID") AND (Orders."Customer ID"=Customers."Customer ID") AND (Orders."Order ID"="Orders Detail"."Order ID") AND ( ( Customers.Country='USA' )) AND (Customers."Customer ID" > @id)`

  Where, id is a parameter created in the catalog.

- In addition, you can use constant level formulas in the SQL file to calculate your data.

  Constant level formulas are formulas which never refer to a DBField, page level special fields (the value of the special field is ready at the time when the report result is generated), or other constant level formulas. The allowed constants that can be referred to by the formulas are:

  - Parameters
  - The special field User Name
  - Constant values, such as aaa
  - Combination of the above three

##### Updating an SQL file

If you make any changes to SQL files in the database, you will need to update them in the connection so that reports built on them can work properly. To do this:

1. Select any SQL file, right-click it and select Update from the shortcut menu.
2. In the Select SQL File dialog, select the SQL files you want to update, and then click Open.
3. In the Select Catalog dialog, select a catalog from the From DB Catalog drop-down list.
4. Click the OK button to close the dialog.

##### Developing reports from SQL files

When you have added some SQL files to a catalog, you can then use them to develop reports as required.

1. Make sure the catalog where the SQL file locates is opened, then click the New Report Set button on the Standard toolbar.
2. In the New Report set dialog, specify the title and layout of the first report in the report set as required, then click OK.
3. In the Data screen of the report wizard, choose the SQL file with which you want to build the report, then follow the report wizard to create the report.

##### Creating cached query results for SQL files

By default, when you run a report, JReport Engine fetches data from the database using the JDBC driver. For reports that are built on imported SQL files which function as queries, JReport enables you to create cached result files and save them somewhere in your machine. Then, when you view these reports, you can choose to use the data from the cached query result file as opposed to the database.

For details about cached query results, see Cached query results.