A Wizard is now available to generate BDL code for your Genero report program (4gl). Select File>>New, Reports, Report from Database (.4gl) to display the dialogs that guide you in creating the SQL query statement that will extract the report data from your database.
Table Selection Page
Select the database and tables for the query.
Figure 1. New Query wizard, Table Section page
Press Next to continue.
Define Joins Page
The New Query dialog is used to add the joins between the tables to be used in the SQL query. The information is pre-filled from the database meta-data in the database meta-schema file (4db). You can select different columns in the tables, or select a different join method.
Figure 2. New Query wizard, Define joins page
- Left column: The table on the left side of the join operator.
- Right column: The table on the right side of the join operator.
- Operator: the method to be used to join the tables for the result set.
Press Next to Continue.
This table illustrates the effect of the operators on the query result set:
| Operator | Description | Example WHERE clause generated for BDL code |
|---|---|---|
| INNER JOIN | Result set will contain columns from only the rows in the two tables where the specified column value matches. | WHERE orders.userid = account.userid |
| LEFT OUTER JOIN | Result set will contain columns from all the rows in the left table, joined with columns from the rows in the right table where the specified column value matches. Where there is no matching value, the columns from the right table in the result set will contain nulls. | WHERE (orders.userid = account.userid OR account.userid IS NULL) |
| RIGHT OUTER JOIN | Result set will contain columns from all the rows in the right table, joined with columns from the rows in the left table in which the specified column value matches. Where there is no matching value, the columns from the left table in the result set will contain nulls. | WHERE (orders.userid = account.userid OR orders.userid IS NULL) |
| FULL OUTER JOIN | Result set will contain columns from all the rows in the left table, and columns from all the rows in the right table, joined on the specified column value. Where there is no match in one of the tables, the columns from that table will contain nulls. | WHERE (orders.userid = account.userid OR account.userid IS NULL OR orders.userid IS NULL) |
Result Set Page
Select the database table fields for the result set of the SQL query. The data in the result set is passed by the BDL REPORT function to the Genero Report Engine and the Report Definition file (4rp).
Figure 3. New Query wizard, Result Set page
Press Next to Continue.
Order By Page
Select the column(s) by which the result set should be sorted.
Figure 4. New Query wizard, Order By page
Press Finish to generate the code and close the Report Data Wizard.
The Wizard creates a BDL file (4gl) containing the generated code, which you can save as part of your application files.