Oracle9i Discoverer For Power Users

Course Description

Experienced Oracle Discoverer users are interested in knowing how the advanced features of the Oracle SQL language can be incorporated into Discoverer queries. Within this 1-day course users will learn how to enhance their queries through the use of correlated subqueries, functions, processing of hierarchies and other advanced SQL techniques. When these advanced techniques are combined with the ease-of-use features of Oracle Discoverer then users are fully empowered to extract crucial information from their databases and data warehouses.
1 day
Contact us for pricing
 

Prerequisites

This course has been developed for all Oracle professionals including developers, database administrators, designers and analysts.

Students should have attended the following course or have equivalent knowledge and experience:

Introduction To Oracle9i SQL
Oracle9i Discoverer For Desktop Users
Oracle9i Discoverer for Administrators

Advanced queries in Discoverer

Fan traps
Multiple path queries
Recursive queries

Analytic functions

The analytic function template
Types of analytic function
Ranking
Banding
Equi-width bands
Equi-height bands
Bands using greatest, case, ntile
Windowing
Reporting aggregates
Lagging/leading
LAG()
LEAD()
Other analytic functions
CUME_DIST()
PERCENT_RANK()
DENSE_RANK()
RATIO_TO_REPORT()
ROW_NUMBER()
STDDEV_POP()
FIRST_VALUE()
LAST_VALUE()
Statistical functions
REGR_SLOPE()
REGR_INTERCEPT()
Analytic functions and conditions

Hierarchical queries (connect by prior)

Using hierarchical relationships
One to many (1:N) hierarchy
Many to many (M:N) hierarchy
Bill-of-materials explosion
Bill-of-materials implosion

Advanced hierarchical queries

Expanding displays for hierarchical queries
One to many hierarchy – any level display
Many to many hierarchy – any level display
Creating the hierarchy queries in Discoverer desktop

Writing and registering a PL/SQL function

The need for PL/SQL functions
Writing the function
Registering the function
Using the function

Alternate date sorts

Changing a date format
Date LOVS
Defining an alternative sort for dates

Discoverer debug trace

Reasons for creating a debug trace
Setting up the debug trace
Generating the debug trace
About the debug trace log file
Using metalink
Discoverer