Index awareness and LOV conflict

[system]

This is the first time I’m using Index Awareness. I’ve set up all my primary and foreign keys and I know they are working correctly. Unfortunately that’s part of the problem I’m having.

My user tried to display a list of values for an object in a table where there is no other lookup table. She wanted a distinct list of codes, but was getting every single row in the table. Definitely not distinct.

When I edited the LOV I checked the SQL that it was generating and the primary key (surrogate numeric column) was appearing as part of the select distinct statement. (That’s how I knew the index awareness was working!) Of course, based on the surrogate key, every row was returned. When I removed the primary and foreign keys I’d defined for the object, I was able to retrieve only the distinct values.

I’ve already set index awareness for all the objects in my universe. I would prefer not having to delete the keys, run the LOV query and reset the keys for every object. How can I keep index awareness set and still generate LOV’s that contain only distinct values?

---

Debbie Kelly (BOB member since 2002-08-15)

[system]

Hi Debbie,

Dave’s answer to my idea here explains the behavior you experience:

Universe and Data Access enhancements

Let’s start a sticky thread to keep track of suggestions and ideas related to the Semantic Layer (the Universes) as well as Data access from those universes. Please post replies with your ideas… dnewton (BOB member since 2004-01-30)

---

Marek Chladny (BOB member since 2003-11-27)

[system]

Thanks Marek and Dave. I realized that the table I was referring to has a sequence number that is part of the primary key and I hadn’t included it. I’ll review all the tables and fix the ones with multiple column primary keys. I’ll let you know if that solved the problem.

Thanks again.

---

Debbie Kelly (BOB member since 2002-08-15)

[system]

It worked! Thanks again.

---

Debbie Kelly (BOB member since 2002-08-15)