If a report needs more than one table, all tables must be added to the report and linked with each other. For this one creates a join between the tables. A join has a join type and one or more column links.
Supported are all join types :
As link types there are the following:
More information about join types and link types at link properties.
The resulting join statement of the SQL query depends on the specific database and of i-net Clear Reports driver options.
There are two tables, "Tab_A" and "Tab_B". They are linked together with a Left Outer Join and an Equal link between the ID columns.
"Tab_A" is the parent table.
With the driver setting "supportsSQL92syntax=true" the resulting SQL statement is the following:
SELECT TAB_A.ID FROM SCOTT.TAB_A TAB_A LEFT OUTER JOIN SCOTT.TAB_B TAB_B ON TAB_A.ID=TAB_B.ID
Note: This Join syntax is only executable on Oracle 9 or higher.
For older Oracle version the flag has to be false.
With the driver setting "supportsSQL92syntax=false" the resulting SQL statement is the following:
SELECT TAB_A.ID FROM SCOTT.TAB_A TAB_A,SCOTT.TAB_B TAB_B WHERE TAB_A.ID=TAB_B.ID(+)
If one or more tables are not joined with the other tables, they will be append to the FROM clause of the SQL statements. Thereby it will be created a cartesian product between these tables. If you press the "ok" button in the "Database Wizard" and not all tables are joined, you will receive a warning box.
i-net Clear Reports tries to realize the join in that way as it is designed. If that is possible is depending on the fact, if all joins together represents a tree structure.
Each join has a from-table and a to-table. If you drag the link from "Tab_A" to "Tab_B", "Tab_A" is the from-table. If every table is maximal one time the to-table, the designed join can be translated directly to SQL. Otherwise it is necessary to swap a join, which has however no influence for the record count.