13
Conditions

See Section 13.1.2, "Condition Types" for more information.

• If you want to create a Condition based on:
  • a single Item, proceed to Section 13.2.1, "Single Item Conditions"

  • multiple Items, proceed to Section 13.2.2, "Multiple Item Conditions"

13.2.1 Single Item Conditions

This section describes how to finish creating a Condition based on a single Item. For example, "Region = East".

1. Select the Item that you want to base your Condition on (using the Item drop-down list).

   The Item drop-down list also enables you to create a Condition based on:

   • a Calculation (choose Create Calculation...), or

   • an existing Condition (choose Select Condition...).

   For more information on creating Calculations, see Chapter 12.2, "Creating Calculations".

2. Select the type of comparison (using the Condition drop-down list).

3. Select the value(s) that are to be compared with the Item (using the Value(s) drop-down list).

   You can also type the values directly into the field:

   • Surround values consisting of alphabetical characters with single quotes (`entry')

   • Do not surround numeric values with quotes.

   • Separate multiple values with commas.

   The Value(s) drop-down list also enables you to create a calculated value to use with this condition (choose Create Calculation...).

4. If you want the Condition to be case sensitive, tick Match Case, otherwise, clear it.

5. Click OK.

For more information about the fields on this dialog box, click Help.

13.2.2 Multiple Item Conditions

This section describes how to finish creating a Condition based on multiple Items. For example, "(Department IN 'Video Sale' or 'Video Rental') AND (Region = Central)".

1. Click Advanced >>.

   This displays the advanced version of the Edit Condition dialog box (see Figure 13-3).

Figure 13-3 New Condition Dialog Box with Advanced Selected

Note that the SQL code for the formula is displayed at the bottom of the dialog box.

• Click Add.

  This adds a new row to the Condition.

• Select the Item that you want to base your Condition on (using the Item drop-down list).

  The Item drop-down list also enables you to create a Condition based on:

  • a Calculation (choose Create Calculation...), or

  • an existing Condition (choose Select Condition...)

  • the formula of an existing Condition (choose Copy Condition...).

For more information on creating Calculations, see Chapter 12.2, "Creating Calculations".Select the type of comparison (using the Condition drop-down list).

4. Select the value(s) that are to be compared with the Item (using the Value(s) drop-down list).

   You can also type the values directly into the field:

   • Surround values consisting of alphabetical characters with single quotes (`entry')

   • Do not surround numeric values with quotes.

   • Separate multiple values with commas.

   The Value(s) drop-down list also enables you to create a calculated value to use with this condition (choose Create Calculation...).

5. Repeat steps 2 to 5 to add more rows to your Condition formula.

6. Click in the Group column and choose how the rows in the Condition formula relate to each other:
   • If all of the rows must be true to display the data, click And.

   • If only one of the rows has to be true to display the data, click Or.

   • If all of the rows must be false to display the data, click Not.

7. If you want the Condition to be case sensitive, tick Match Case, otherwise, clear it.

8. Click OK.

For more information about the fields on this dialog box, click Help.

13.3 Editing Condition Properties

Condition properties are accessible through Condition Properties dialog boxes. This section shows you how to enhance the user's view of the data by editing Condition properties. Figure 13-4 shows an example Condition Properties dialog box.

Figure 13-4 Condition Properties Dialog Box with the General Tab Selected

13.3.1 Editing the Properties of a Single Condition

This section describes how to edit a Condition's properties.

1. Open the Condition's Properties dialog box.

   There are four ways to do this:

   • Double-click
     Double-click the Condition on the Data page.

   • Popup Menu
     Right-click the Condition on the Data page and choose Properties on the popup menu.

   • Toolbar Icon
     Click the Condition on the Data page and click the Properties toolbar icon ()

   • Menu
     Click the Condition on the Data page and choose Edit | Properties.

2. Make your changes as required.

   For more information on the fields on this dialog box, click Help.

3. Click OK.

13.3.2 Editing the Properties of Multiple Conditions

The following steps show you how to set common properties for more than one Condition at a time:

1. Select all of the Conditions whose properties you want to edit.
   (Ctrl-clicking enables you to select more than one Condition.)

2. Display the Condition Properties dialog box.

   There are three ways to do this:

   • Popup Menu
     Right-click one of the selected Conditions on the Data page and choose Properties on the popup menu.

   • Toolbar Icon
     Click the Properties toolbar icon ()

   • Menu
     Choose Edit | Properties.

   All properties that are common to each of the selected Conditions are displayed. If the data for a field is not common to each of the selected Conditions, the field is blank.

3. Make your changes as required.
   Any changes you make here will be applied to all of the selected Conditions.

   For more information on the fields on this dialog box, click Help.

4. Click OK.

13.4 Editing Conditions

This section describes how to edit an existing Condition.

1. Display the Edit Condition dialog box (see Figure 13-2).

   There are three ways to do this:

   • Popup Menu
     Right-click the Condition on the Data page and choose Edit Condition... on the popup menu.

   • Menu
     Click the Condition on the Data page and choose Edit | Edit...

   • Condition Properties Dialog Box
     Click in the Formula field on the Condition Properties dialog box.

   The Edit Condition dialog box works in the same way as the New Condition dialog box (see Section 13.2, "Creating Conditions" for more information).

2. Edit the Condition as required.

3. Click OK.

13.5 Deleting Conditions

This section describes how to delete Conditions.

1. Select the Condition(s) that you want to delete.

   To select more than one Condition at once, hold down Ctrl while you click on the Conditions.

2. Delete the Condition(s):

   There are three ways to do this:

   • Popup Menu
     Right-click one of the selected Condition(s) and choose Delete Condition... on the popup menu.

   • Menu
     Choose Edit | Delete.

   • Keyboard
     Press Delete.

   This opens the Confirm Delete dialog box.

3. Click Impact.
   This displays the Impact dialog box that shows any other objects that may be affected by the deletion (Figure 13-5). The Impact dialog box helps you to make the right choice.

Figure 13-5 The Impact Dialog Box

4. When you have finished reviewing the impact this action will have, click OK.

5. If you still want to delete the selected Condition(s), click Yes.

13.6 Example Conditions

13.6.1 Sales in the Last Seven Days

This example describes how to create a Condition that only returns the Sales in the Last Seven Days. It uses the Calculation Item, "Transaction Age (in Days)".

1. Create a new Condition:
   • Name: Sales in the Last Seven Days

   • Item: Transaction Age (in Days)

   • Condition: <

   • Additional calculations required: Transaction Age = FLOOR (SYSDATE - Transaction Date)

   • Value(s): 7

This is sometimes described as a "rolling window" Condition because the "window" of rows it returns changes from day to day.

13.6.2 Shipments in Q3

This example describes how to create a Condition that only returns Shipments made in Q3 (regardless of year). It uses the Calculation Item, "Ship Quarter".

1. Create a new Condition:
   • Name: Shipments in Q3

   • Item: Ship Quarter

   • Condition: =

   • Additional calculations required: Ship Quarter = EUL_DATE_TRUNC(Ship Date, "Q")

   • Value: Q3

This Condition will now only return Shipments that were made in Q3 (regardless of year).

13.6.3 Outer Join with Conditions behavior

This example shows how the registry setting DisableAutoOuterJoinsOnFilters can affect the result set when a condition (filter) is applied in Discoverer (see Chapter E.2, "Registry Settings"). The effect that this registry setting has is best understood by using an example.

The table below summarizes the conditions used in the examples that follow:

Registry Value	Condition Applied?
1 or 0	No (See example 1)
1	Yes (See example 2)
0	Yes (See example 3)

Example 1 - Shows the rows fetched from a query where an outer join exists between the dept and emp tables (no condition applied):

select dname, ename, job from dept, emp where dept.deptno = emp.deptno (+);

DNAME	ENAME	JOB
SALES	GRIMES	DIRECTOR
SALES	PETERS	MANAGER
SALES	SCOTT	CLERK
SUPPORT	MAJOR	MANAGER
SUPPORT	SCOTT	CLERK
ADMIN
MARKETING
DISTRIBUTION

Example 2 - User adds a condition to the query in Example 1 and the registry setting is set to 0 (default behavior):

select dname, ename, job from dept, emp where dept.depno = emp.deptno (+) and job (+) = 'CLERK';.

DNAME	ENAME	JOB
SALES	SCOTT	CLERK
SUPPORT	SCOTT	CLERK
ADMIN
MARKETING
DISTRIBUTION

Note: Outer joins will not be placed on 'IN', 'IS NULL' or 'IS NOT NULL' clauses as this is not a supported operation by the database.

Example 3 - User adds a condition to the query in Example 1 and the registry setting set to 1:

select dname, ename, job from dept, emp where dept.deptno = emp.deptno(+) and job = 'CLERK';

DNAME	ENAME	JOB
SALES	SCOTT	CLERK
SUPPORT	SCOTT	CLERK