Monday, May 19, 2008

Using CASE to solve Outer Join issues

As many of you know, one of the services that my company offers is Discoverer workshops. What this means is that we send one of our experienced trainers on site and work, sometimes one on one, with the end users on their real system to help them generate the reports that they need. Sometimes I get to go and while I would like to do this more often I just don't have enough time so when the chance does come around I jump at it.

Anyway, one of the most frequent problems that I come across with end user reports is how to effectively create and work with reports when two or more of the folders have outer joins. Such a situation happened in one of the recent workshops that I conducted.

Picture if you will an EUL with 2 folders, folder A and folder B, where folder A is the master being outer joined to B. Thus, if you include something from both folders you will get one row for every item in folder A even if there is nothing in folder B. This is the benefit of the outer join.

Let me get more specific. Let's say that we are working in Human Resources where folder A is Employee Master and folder B is for Employee Leave. Because not all employees have taken leave there is an outer join between the two. Now let's say that in the Leave folder there is an item called Leave Desc that contains descriptions to indicate what kind of leave the employee had taken. With me so far? Here's a screenshot:

Here's some example output:

Ok, so now we want to build a report that allows the end user to exclude one or more Leave Desciptions from the report. To be specific, We want to see all Employees who either have not been sick or have not taken any leave yet. Looking at the report we should be including Carol, Michael and Susie, but not George.

Simply creating the following condition will not work:

Leave Desc <> 'Sick'

Here is that output:

As you can see, George is still included but now appears to have not taken any leave, which isn't true. How about if we also say that Leave Desc IS NOT NULL?

This doesn't give us the right answer either because now both George and Carol have been omitted.

Let's create this calculation which we will call Exclusion:

CASE WHEN "Leave Desc" = 'Sick' THEN 1 ELSE 0 END

We now get this answer, which as you can see has annotated the rows we do not want with 1.

You might think that adding this condition: Exclusion <> 1 would work but it will not. Try it for yourself and you will see what I mean.

You actually need to do add a Boolean OR condition, like this:

Exclusion <> 1
Exclusion <> 1

Problem solved - isn't it? Try removing the Exclusion from the report and see what happens. In Plus you will get the right answer. However in Desktop you will find that your sorting goes wrong. The solution in Desktop is to add a hidden group sort on the Exclusion!

The problem is solved because of the way the CASE statement works which dictates that the function will end as soon as a true condition is encountered. So even there are NULL records these will all get Exclusion of 0 which is the default.

Friday, May 16, 2008

How to find Discoverer patches

A few people have contacted me recently saying they were having troubles finding the latest Discoverer patches. To help you, here is a workflow:

  1. Launch Metalink and enter your login ID and password
  2. Click the Patches & Updates tab
  3. Click the Simple Search link
  4. Change Search By from Patch Number/Name to Product or Family
  5. Enter Oracle Discoverer Family in the search box
  6. Change Release to iAS
  7. Set both Patch Type and Classification to Any
  8. Select your operating system or use Microsoft Windows (32-bit)
  9. Click the Go button
  10. The patches will be listed in order of release with the most recent first

Wednesday, May 07, 2008

Discoverer update

August 2008 Important Update: Oracle released CP1 for Discoverer on August 7th (link to patches)

In a recent posting of mine (link) I advised you not to upgrade to Discoverer just yet.

I have heard that Oracle is working on a critical patch update for that will incorporate all of the critical patches (CP4, CP5 and CP6) plus fix any specific issues that are unique to that release. The latest information I have is that you should expect to see the patch before the end of the summer. I don't have any better timeline than this I'm afraid. As soon as I hear something more definite I promise I will let you know.

Therefore, for the time being I still recommend not upgrading to You should, however, upgrade to and apply the most recent critical patch (link).