6
Lesson 5: Working with custom folders

Lesson 5: Working with custom folders

It will probably take you about 5 minutes to complete this lesson.

In this lesson you will enhance the business area by creating a custom folder and learning how to edit the SQL statement that defines the custom folder.

A Discoverer folder is a SQL statement that references data in the database, it represents data much like a database view. The simple folders that you have worked with until this stage, refer to SQL statements that are stored in the End User Layer.

Discoverer Administrator enables you to create custom folders based on arbitrary SQL statements that you specify. You can use set operators, such as UNION, CONNECT BY, MINUS, INTERSECT, and synonyms and quickly set up a custom folder that represents a complex result set.

This lesson consists of the following exercises:

• "Exercise 1: Creating a custom folder"

• "Exercise 2: Editing the SQL statement in a custom folder"

Exercise 1: Creating a custom folder

In this exercise you will create a custom folder that creates a local list of values within the EUL for an item with a small number of possible values. The exercise enables days of the week to be displayed in their logical order rather than alphabetically. The list of values to be created (e.g. days of the week) has a numeric sequence that we will use later in the tutorial (for more information, see "Lesson 7: Customizing items").

You will use a SQL statement to create a custom folder that has two items, one is a list of values representing the days of the week (DAY_OF_WEEK) and the other represents an alternative sort sequence (ALTERNATE_SORT).

To create a custom folder:

1. Display the Workarea: Data tab and click the New Video Stores business area.

2. Choose Insert | Folder | Custom to display the Custom Folder dialog.

Figure 6-1 Custom Folder dialog

Text description of the illustration custflu.gif

3. Type (or cut and paste) the following SQL statement into the Custom Folder dialog:

   select 'Monday'   DAY_OF_WEEK,
           1         ALTERNATE_SORT
   from dual
   union
   select 'Tuesday'  DAY_OF_WEEK,
           2         ALTERNATE_SORT
   from dual
   union
   select 'Wednesday' DAY_OF_WEEK,
           3         ALTERNATE_SORT
   from dual
   union
   select 'Thursday'  DAY_OF_WEEK,
           4         ALTERNATE_SORT
   from dual
   union
   select 'Friday'   DAY_OF_WEEK,
           5         ALTERNATE_SORT
   from dual
   union
   select 'Saturday' DAY_OF_WEEK,
           6         ALTERNATE_SORT
   from dual
   union
   select 'Sunday'   DAY_OF_WEEK,
           7         ALTERNATE_SORT
   from dual
   

4. Type Alternate Sort Days into the Name field to replace the default text CustomFolder.

Figure 6-2 Custom Folder dialog

Text description of the illustration custfldr.gif

5. Click Validate SQL.

   Discoverer checks the SQL statement and displays a prompt that tells you whether it is valid.

   If the SQL statement is not valid:

   1. Click OK to close the prompt.

   2. Correct the SQL statement.

   3. Click Validate SQL again.

   If the SQL statement is valid:

   1. Click OK to close the prompt.

   2. Click OK to close the Custom Folder dialog and create the custom folder.

   Figure 6-3 Workarea: Data tab

   
   
   Text description of the illustration custflba.gif

   Discoverer displays the new custom folder in the business area. Notice the SQL label on the folder icon. This identifies that this is a custom folder as distinct from a simple or complex folder.

   In this exercise you created a custom folder that created a local list of values within the EUL for an item with a small number of possible values.

   Exercise 2: Editing the SQL statement in a custom folder

   In this exercise you will learn how to edit the SQL statement in a custom folder.

   To edit the SQL statement in a custom folder:

   1. Display the Workarea: Data tab and click the Alternate Sort Days custom folder.

   2. Choose Edit | Properties to display the Custom Folder Properties dialog.

      The Custom SQL field displays the SQL statement that defines the folder. You can resize the Custom Folder Properties dialog to view more of the SQL statement.

   Figure 6-4 Custom Folder Properties dialog

   
   
   Text description of the illustration custflp.gif
   3. Click the Custom SQL field to display the Edit Custom Folder dialog that contains the SQL statement.

   Figure 6-5 Edit Custom Folder dialog

   
   
   Text description of the illustration custfle.gif

   Do not edit the statement. The purpose of this step is to show you how to access the Edit Custom Folder dialog so you know how to edit the SQL statement if required.

   4. Click Cancel to close the Edit Custom Folder dialog.

   5. Click Cancel to close the Custom Folder Properties dialog.

   In this exercise you learned how to edit the SQL statement in a custom folder.

   Lesson summary

   In this lesson you enhanced the business area by creating a custom folder and learned how to edit the SQL statement that defines the custom folder.

   In this lesson you completed the following exercises:

   • In Exercise 1 you created a custom folder.

   • In Exercise 2 you learned how to edit the SQL statement in a custom folder.

   In the next lesson you will create joins between folders.