Design

Simple SQL Queries can be designed using Design Tab.

To add an entity in Select clause and From clause:

1. Select Table, Procedure, View or Synonym. List of entities of selected item appears.

2. Drag the entity you want from the Entity list and drop it in design area (blank area on the right).

It appears as a box in design area.  Entity name also appears in Select clause and From clause.  Repeat the above steps to select multiple entities.

Note: Instead of drag and drop option, you can also double-click an entity. You can also use parameters as part of the SQL. To do this, drag [PARAMETERS] to design area. Parameter box appears in 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:

Drag the field / attribute from one entity list and drop it on the field / attribute on another entity.

An arrow will appear connecting two attributes of ‘From’ entity and ‘To’ entity. Select, From and Where clause will also change accordingly.

To delete a join:

Right-click the arrow representing that join. The Delete Link option appears. Click Delete Link option.

Join will be deleted and will be removed from Where clause.

Working with Attributes

• An attribute from any entity on design area can be dragged and dropped at right place in Select clause, Where clause, Group By and Order By clause.

• You can also select or clear the attribute to include it and remove it from Select clause respectively.

Where clause

To get a row at the top:

Click the  in the leftmost cell of column header. This inserts a row below column header.

To get a row below current 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  button in respective condition row.

To specify a where clause

• Form a condition by selecting operand1, operand2 and operator.

• To join conditions, form two conditions and select a relation in rightmost column of the first condition (of the two being joined).

• To group conditions, specify opening and closing braces.

Note: You can also drag an attribute from design area to be used as operand1 or operand2.

Group By

In the Group By clause you can provide grouping criteria for the SQL statement. To place an entity in Group By, click the entity in the Entity List and drag it in the box below Group By.

Note: You can also drag an attribute from design area.

Having

The procedure to complete Having clause is the same as that of Where clause.

Note: Do include appropriate summary function in select clause so that it can be used in 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.

Note: You can also drag an attribute from design area.