Home » Fusion Middleware & Colab Suite » Business Intelligence » Sorting is different in OBIEE 10.1.3.4.0 and 10.1.3.4.2
Sorting is different in OBIEE 10.1.3.4.0 and 10.1.3.4.2 [message #542798] Fri, 10 February 2012 03:53 Go to next message
lkolluru
Messages: 3
Registered: February 2012
Location: Hyderabad
Junior Member
I have a column which is varchar(4) and has data as numbers (1,2,3, .... 31).

When i view the column data from "Answers" in OBIEE 10.1.3.4.0, the column data is shown in ascending order 1 to 31 and the query shown is "SELECT Calendar."Day of Month" saw_0 FROM "P6 Project History" ORDER BY saw_0" --- the sort is similar to integer sort.

When the same column data is viewed from "Answers" in OBIEE 10.1.3.4.2, the column data is shown as 1,10,11,12,13,14,15,16,17,18,19,2,20,21,22,23,24,25,26,27,28,29,3,30,31 and the query shown is "SELECT Calendar."Day of Month" saw_0 FROM "P6 Project History" ORDER BY saw_0" ---- Here sort is happening considering the data as individual chars for same query.

NOTE: I am using same DB and same RPD file in both OBIEE versions.

Could you please tell me if this is an expected change in 10.1.3.4.2 or is it due to some other reason.

Thanks
Re: Sorting is different in OBIEE 10.1.3.4.0 and 10.1.3.4.2 [message #542799 is a reply to message #542798] Fri, 10 February 2012 03:57 Go to previous messageGo to next message
Littlefoot
Messages: 19905
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
I don't use OBIEE, but - if possible, change ORDER BY clause to
order by to_number(saw_0)

By the way, if numbers are stored into that column, why is it declared as a VARCHAR2 instead of NUMBER?
Re: Sorting is different in OBIEE 10.1.3.4.0 and 10.1.3.4.2 [message #542803 is a reply to message #542799] Fri, 10 February 2012 04:02 Go to previous messageGo to next message
lkolluru
Messages: 3
Registered: February 2012
Location: Hyderabad
Junior Member
We can get the required result by tweaking the query. But, I want to understand why the difference is seen in both the versions when everything else is same. Is it expected change in the new version or is it done by changing anything in the server or is it a bug.
Re: Sorting is different in OBIEE 10.1.3.4.0 and 10.1.3.4.2 [message #542806 is a reply to message #542803] Fri, 10 February 2012 04:05 Go to previous messageGo to next message
Littlefoot
Messages: 19905
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
It is obvious that characters are sorted differently than numbers (for example, '11' comes before '2').

As I said, I don't use OBIEE; maybe there's some option (checkbox, parameter setting, whatever) that tells the engine to do sorting by numbers and something internally, behind the scene, rewrites your ORDER BY clause.

Really, no idea. Someone who uses OBIEE might know the answer.
Re: Sorting is different in OBIEE 10.1.3.4.0 and 10.1.3.4.2 [message #542829 is a reply to message #542798] Fri, 10 February 2012 05:43 Go to previous messageGo to next message
vinkum
Messages: 19
Registered: October 2011
Location: India
Junior Member


It is expected in version 10.1.3.4.2

Oracle did some fixes catalog manager in this version

Previously OBIEE do sorting based on the data.
In this version they changed "Sorting by type"

For More Information Refer this
http://docs.oracle.com/cd/E10415_01/doc/bi.1013/e10416.pdf
Topic 8.3 General Fixes of catalog Manager. Smile
Re: Sorting is different in OBIEE 10.1.3.4.0 and 10.1.3.4.2 [message #542831 is a reply to message #542829] Fri, 10 February 2012 05:50 Go to previous message
lkolluru
Messages: 3
Registered: February 2012
Location: Hyderabad
Junior Member
Thank you Vinkum for the help.
Previous Topic: Can Drill-down paths being made dependent on the user roles?
Next Topic: Master Detail Model Editor Problem
Goto Forum:
  


Current Time: Mon Dec 29 07:55:53 CST 2014

Total time taken to generate the page: 0.20095 seconds