The attribute Class Standing (found in the student objects) can tell us if an admitted undergraduate is a freshman, sophomore, junior or senior. It can also tell us if a law student is first, second or third year. But for other graduate students and for nonadmitted students, Class Standing will be blank.

In this example, we want to create a computed attribute that will fill in the blanks with useful information.

This is how we want to define our new attribute "Class Standing Desc Improved":

• Look at the attribute Student Level Code.
• If it has a value of 'GR' then use Student Type Description.
• If it has a value of 'NU' then use Student Level Description.
• If it has a value of 'NG' then use Student Level Description.
• For any other value of Student Level Code use Class Standing Description.

DECODE(Student Level Code, 'GR', Student Type Description, 'NU', Student Level Description, 'NG', Student Level Description, Class Standing Description)

To get a good understanding of how the DECODE function works take some time to closely compare the definition and how we wrote it as a function.

To build the computed function in GQL:

1. Go the the All Terms Two model and choose New Query.
2. Open the All Students object and choose "calculation..." from the function box next to the Class Standing Desc attribute. (Picking the Class Standing Desc attribute will cause your new computed attribute to appear directly beneath the Class Standing Desc attribute in the attribute list).
3. You should now see the Edit Calculation window. Notice the Expression box. This is where you build your computed attribute. Click once in the box to begin (you should get a blinking cursor in the box). You can build an expression by a combination of typing, inserting attributes, inserting functions, and/or using the keypad buttons.

   Select the Student Level Code attribute from the scrolling Table/Attributes window by clicking on it once.

4. Press the insert function button and select "Decode(x,expr,search)" from the list of "Other functions" on the right. Press the Insert button.
5. In the Expression box: Highlight and delete "expr" and in its place type 'GR' (include the single quotes). Delete "search" and click on Student Type Desc in the Table/Attributes window by clicking on it once. Then click on the Insert Attributes button.
6. Continue to build the function, by typing and inserting attributes making sure that you separate the arguments with commas and that all of the arguments are contained within parentheses. When you are done the function should look like this:

   decode(DATAMGR.DWS_ENRLSTD.STUDENT_LEVEL_CODE, 'GR', DATAMGR.DWS_ENRLSTD.STUDENT_TYPE_DESC ,'NU',DATAMGR.DWS_ENRLSTD.STUDENT_LEVEL_DESC ,'NG',DATAMGR.DWS_ENRLSTD.STUDENT_LEVEL_DESC , DATAMGR.DWS_ENRLSTD.CLASS_STANDING_DESC)

   Compare this code to the way we originally sketched out our decode function. You'll notice that the attribute names have been changed to the more technical sounding Oracle names and the spacing is different but otherwise it is identical.

7. Type in "Class Standing Desc (Improved)" as the name for your new computed attribute and click on the OK button.
8. Your new computed attribute should appear on the attribute list. Test it. If it doesn't work correctly, you can edit your new attribute by choosing "Edit calculation..." by holding down the function box next to the new attribute. You can also choose "Delete calculation" if you want to remove your new attribute.
9. To save your new attribute for future warehouse sessions choose "save" from the File menu.

   ---

   This page was last updated on March 31, 1997.
   [Home | Announcements | Access | Installation | User Notes | Data Dictionary | Mailing List | Contact]