LOVs - Oracle 10g vs 9i

building semantic layer
1

Hi all–

I’ve been browsing around at LOV topics and think others have this issue, but haven’t couched it the same way. We noticed, after we upgraded our Warehouse to 10g in December, that the default sort for universe object LOVs is no longer alphabetical, as it is for 9i databases (I confirmed by checking a universe that queries 9i data). We make a lot of use of default LOVs and don’t relish the prospect of now having to customize potentially hundreds that had been fine the way they were under 9i. I’ve submitted a tech support case in the office chance that there’s an XI patch, but I’ve confirmed this behavior affects universes in XIR2, XIR1 and even 5.x.

Has anyone come up with a creative way to address this? Re-sorted all the data in the tables isn’t an option :smiley:

Thanks
Amy

Amy Miller :us: (BOB member since 2002-06-07)

2

Amy – it’s the case in Sybase that the LOV isn’t sorted by default in any of the versions that we’ve used. If we don’t want to create a custom LOV, we teach our ZABO users that they can click the heading of the LOV column, and that will sort the results.

Our WebI users don’t have that option, however, so for LOVs that really need to be sorted – we have to do a custom LOV.

I wonder if there’s a setting on your Oracle 10g universe that could make it behave like 9i does?

Or, I just did a search and found this topic which might be of interest to you to try – someone changed a BusObj parm, which may or may not work in your case …

Anita Craig :us: (BOB member since 2002-06-17)

3

Hmmm… according to the helpfile, that has more to do with table display, I think:

Just tried it locally and it didn’t help.

Clicking the header is a good point though, for BusObj users. Doesn’t help our folks using Crystal against a universe - the user interface for the LOV picklist doesn’t have anything one can click on.

Thanks though!

Amy Miller :us: (BOB member since 2002-06-07)

4

Yeah, it’s too bad that the developers didn’t give the WebI and Crystal folks the same option that the full-client/ZABO Reporter folks have.

I doubt that anyone’s ever put “being able to click the heading of a LOV to sort it” on the list of what’s in full client that’s not in WebI.

Anita Craig :us: (BOB member since 2002-06-17)

5

There’s apparently a parameter that can be used in XI - FORCE_SORTED_LOV - we’ll be testing that, but it seems promising. No word yet from tech support if there’s a workaround for 5.x.

Amy Miller :us: (BOB member since 2002-06-07)

6

Hi Amy,

I posted in the other linked thread and managed - in my wisdom -to suggest the wrong parameter :oops: .

It should be the one you said:-

FORCE_SORTED_LOV

Change to ‘Yes’, this is present I think from 6.x onwards and does work!

As far as 5.x goes you may be able to add this value to your prm file for Oracle.

Mak 1 :uk: (BOB member since 2005-01-06)

7

I’ve the same problem with results of LOV’s with different order since installation of Oracle 10g.

I’ve reading about it in metalink (oracle official portal) and I’ve found information about a new parameter in BBDD named ‘_windowfunc_optimization_settings’. The description of error is as follow: It is possible for a query that uses window functions to return rows in a different order than the specified by the ORDER BY clause

In my case without order by, only clicking in name column on top for order by description.

Oracle says a Workaround:
alter session set “_windowfunc_optimization_settings”=8

I have not information about this new parameter, and I don’t have modified it in my BBDD, I’m looking for more information before alter system.

Any suggestion, any idea?

DRBO :es: (BOB member since 2008-07-08)