Interesting condrum with indexed values

As you know from a previous posting it is possible to create Discoverer lists of values to display a lookup description e.g. Department Name and return the primary key code namely, Department Id when using a list of values in a parameter.

What I have discovered today is something very intersting in the way that Discoverer does this and something that you ought to be aware of.

Let's say a bottle manufacturing company has a whole series of bottles which they describe using the color of the top. Some descriptions would be 1 Litre Blue Top, some 1 Litre Red Top and so on. Let's also say that they start out with code 10 represting 1 Litre Blue Top, and code 11 representing 1 Litre Red Top.

Inside Discoverer Plus, if a user has an indexed item in use and they choose to select using the Values option (see my original posting if you are unsure what this looks like) they will be presented with a list of values like this:

• (1 Litre Blue Top) 10
• (1 Litre Red Top) 11

What you may not be aware of is what Discoverer submits to the database. This is what will get submitted:

(((o100539.PRODUCT_DESCRIPTION) = '1 Litre Blue Top'
AND o100539.PRODUCT_CODE = '10'))

Yes, Discoverer actually adds an AND clause for BOTH parts of the equation. It searches for both the Code and the Description. I can see why this is happening. This is to solve the following problem:

Let's say the bottling company later decide to introduce an updated version of the 1 Litre Blue Top and decide to use code 20. Because there are two items with the same description, if Discoverer were to only submit this:

(o100539.PRODUCT_DESCRIPTION) = '1 Litre Blue Top' )

then both items would be returned in the query. The only way to guarantee to get the right combination would be for Discoverer to add the AND clause.

Somebody recently asked me why Discoverer didn't use an OR connector, like this:

(((o100539.PRODUCT_DESCRIPTION) = '1 Litre Blue Top'
OR o100539.PRODUCT_CODE = '10'))

If Oracle were to change the code to an OR condition then we would definitely get multiple rows returned so Oracle's choice of using an AND clause seems to be correct.

So why then am I bringing this to your attention. I'll tell you why.

Let's say the bottling company decides to rename the description for bottle 10 to this 1 Litre Top - Blue. No big deal you might think because there is still only one description for that code - wrong!

Let's say a user opens a workbook containing a worksheet that has a such an indexed parameter and that the last time it was used either the code 10 was selected or it is being supplied as the default value. Because the parameter already has the value displayed on screen all the user needs to do is to accept the current value and click Finish. Because the user does not reselect 10 and just clicks Finish what gets submitted to the database will still be the original AND clause:

(((o100539.PRODUCT_DESCRIPTION) = '1 Litre Blue Top'
AND o100539.PRODUCT_CODE = '10'))

Yes, now you can see the conundrum. No data is returned because there is no product called 1 Litre Blue Top in the database. Discoverer does not reassess the situation when an existing parameter is used. Is this a bug? Maybe. I will send this to Oracle for comment but I thought you would like to know.

I think the workaround would be to pick a new paramater value from the list, then go back and repick the original value. Assuming the worksheet now returns data the worksheet needs to be resaved. Of course, this also assumes that the user using the worksheet is the worksheet owner. If they are not the owner then the problem will persist until the owner can be notified.

As I say - an interesting conundrum!