Simple SQL Queries can be designed using Design Tab.
To add an entity in Select clause and From clause:
...
Adding Entities to the SELECT and FROM Clauses
Select an entity type: Choose Table, Procedure, View, or Synonym. List A list of entities of for the selected item type appears.
Add an entity: Drag the an entity you want from the Entity list and drop it in the design area (the blank area on the right).
...
The entity appears as a box in the design area
...
, and its name is added to the SELECT and FROM clauses.
Repeat this process to add multiple entities.
...
Alternative methods:
Double-click an entity
...
to add it to the design area.
To use parameters, drag [PARAMETERS] to the design area.
...
A parameter box
...
will appear in a different color.
To re-position attribute list of an entity on design area:
Click the title bar of the box and drag to re-position it where you wish on design area.
To join two entities:
...
Repositioning an Entity’s Attribute List in the Design Area
Drag the entity's title bar to move it to the desired location in the design area.
Joining Two Entities
Drag a field/attribute from one entity
...
and drop it on
...
a field/attribute
...
in another entity.
An arrow will
...
connect the two fields, and the SELECT, FROM, and WHERE clauses will update automatically.
Deleting a Join
Right-click the arrow representing
...
the join.
...
Select Delete Link
...
.
...
The join is removed, and the corresponding change is reflected in the WHERE clause.
Working with Attributes
...
Attributes from any entity
...
in the design area can be dragged and dropped
...
You can also select or clear the attribute to include it and remove it from Select clause respectively.
Where clause
To get into the SELECT, WHERE, GROUP BY, or ORDER BY clauses.
Include or remove attributes: Select or deselect an attribute to add it to or remove it from the SELECT clause.
WHERE Clause
Adding and Managing Rows
Insert a row at the top:
...
Click
...
the + icon in the leftmost cell of the column header.
...
Insert a row below
...
a specific row:
...
Click in the row below which you want to add a row for condition. A row will be inserted in the row below the respective row.
...
To remove a condition:
...
Click the + icon in the desired row.
Remove a row: Click the - button in the respective condition row.
...
Specifying Conditions
...
Define a condition by selecting operand1, operand2, and the operator.
...
Join conditions: Form two conditions and select a relation (e.g., AND, OR) in the rightmost column of the first condition
...
.
...
Group conditions: Use opening and closing braces to group conditions.
| Panel | |||
|---|---|---|---|
|
...
| |||
Note You can |
...
drag an attribute from the design area to |
...
use it as operand1 or operand2. |
Group By
...
GROUP BY Clause
The GROUP BY clause specifies grouping criteria for the SQL statement. To place
Drag an entity
...
or attribute from the Entity List or design area and drop it in the box
...
Note: You can also drag an attribute from design area.
Having
...
under GROUP BY.
| Panel | ||||||
|---|---|---|---|---|---|---|
| ||||||
Note Ensure to use attributes relevant to the grouping criteria. |
HAVING Clause
The procedure for specifying the HAVING clause is the same as that of Where clause.
...
the WHERE clause.
| Panel | ||||||
|---|---|---|---|---|---|---|
| ||||||
Note Include an appropriate summary function in |
...
the SELECT clause so |
...
it can be used in |
...
the HAVING clause. |
Order By
In the Order By clause you can provide sorting (ascending/ descending) criteria for the SQL statement. For a report with grouping, the order by clause must have the columns in the same order as of the respective sections in the Layout Editor.
...
ORDER BY Clause
The ORDER BY clause specifies sorting criteria for the SQL statement (ascending or descending).
Drag an entity or attribute from the Entity List or design area into the ORDER BY section.
| Panel | ||||||
|---|---|---|---|---|---|---|
| ||||||
Note For grouped reports, the ORDER BY clause must align with the column order in the respective sections of the Layout Editor. |