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 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.
Cost: Price on application
Duration: 1 Day
This course is not available as part of our public schedule but can be provided on a customised client specific basis.
Participants would need to have a good working knowledge of Oracle 9i Discoverer or have attended an Oracle 9i Discoverer Administration course.
Advanced Queries in Discoverer (1):
Fan Traps, Fan trap detection What do these messages mean? Disabling the Fan Trap Detection Option Why are the totals wrong? How do we get out of the Trap? Why does the query work now? Other Fan Trap Considerations Multiple Join Paths, Creating a Query with Multiple Paths
Advanced Queries in Discoverer (2):
Join Folders Recursive Queries
Analytic Functions (1):
What are Analytic Functions? The Analytic Function Template Template Example Types of Analytic Functions Ranking, Banding Equi-Width Bands, Equi-Height Bands Bands Using GREATEST, CASE, and NTILE Windowing
Analytic Functions (2):
Moving Average, Cumulative Total Lagging/Leading Statistical Functions Slope, Intercept Analytic Functions and Conditions Applying Criteria in Seperate Conditions Applying Criteria in One Condition
Hierarchial Queries (Connect by Prior) (1):
Why we need Hierarchial Relationships Problems seeing the Hierarchy One to Many (:N) Hierarchy :N Hierarchial Query Many to many (M:N) Hierarchy BOM Illustrated M:N Hierarchy (Bill-of-Materials Table Definition) Populating the Part Table
Hierarchial Queries (Connect by Prior) (2):
Populating the BOM Table Bill-of-Materials Explosion Bill of Materials Implosion
Advanced Hierarchial Queries (1):
Expanding Displays for Hierarchial Queries Setting Up the :M Hierarchail Query for Multi-level Display Building Hierarchail Query Custom Folders Joining the Custom Folders Hide the Linked Folders Create the Folder the Users will Access Creating the Query in the User Edition Setting Up the BOM Hierarchial Query for Multi-Level Display
Advanced Hierarchial Queries (2):
Building BOM Hierarchial Query Custom Folders Joining the BOM Custom Folders Creating the Parts Explosion Folder that the Users will Access Creating the BOM Query in the User Edition Customizing the BOM Query
Writing and Registering a PL/SQL Function (1):
The Need for PL?SQL Functions Writing the Function Create the function specification Declare Variables, Program Logic The Exception Section, The End Statement Building the Function Preliminary Test Registering the Function
Writing and Registering a PL/SQL Function (2):
Registered Function Argument Description Using the Function
Alternative Date Sorts:
Changing a Date Format Effect on the Date LOV Defining and Alternate Date Sort
Discoverer Debug Trace:
Reasons for Creating a Debug Trace Setting up The Debug Trace, The Debug Trace Log File The Debug Tracve Diagnostic Levels Generating the Debug Trace File Using Metalink What Causes This Error? How might we prevent this problem (Or live with it)?