Max of an Object

building semantic layer
1

Hi All,

I had put a similar question sometime back…no one ( except Ramakanth) really help me get an answer to thisI have a report with 3 fields,
I am not yet able to do it.

So here it goes…
I have a report with 2 fields…

Name ( name of Employee)
Schooling Degree( Department Number)
Weightage of degree( eg. “Bachelor = 1”, “Masters=2” and “doctorate=3”…which I have written as to_number(decode( SCHOOLING.DEGREE
,‘Associates’,‘1’,‘Bachelors’,‘2’,‘Masters’,‘3’,‘Doctorate’,‘4’,‘Doctoral Degree’,‘4’,‘0’)) as the object definition )

Now when I run the query, I get 3 components for the same employee.

At present it looks like this…

Name Schooling Degree Weightage

Listserv Archives (BOB member since 2002-06-25)

2

What we tried was to pull in all of the academic information without a decode field - just bring in the name and the academic fields. Once within the report, we created a Variable with a qualification of a measure, that assigned a value for each degree. Next we break the report by name, apply a ranking based on the academic degree field, and select the top 1 record. There is some further cleanup of the report such as hiding the break row, but we have been successful with this process. Hope this helps.

p parks
Dell Computer Corp

Listserv Archives (BOB member since 2002-06-25)

3

Parks,

How do you select the select the top 1 record ? Thanks
Ashish

Listserv Archives (BOB member since 2002-06-25)

4

Hey, Ashish.

You are correct to do this with the Calculation wizard. Maybe there’s a problem with the SQL it is trying to generate. Given my assumptions about your table names and joins, does the following SQL work when written directly to the server?

SELECT
Person.Name
Schooling.Degree
to_number(decode(
Schooling.Degree,‘Associates’,‘1’,‘Bachelors’,‘2’,‘Masters’,‘3’,‘Doctorate’, ‘4’,‘Doctoral Degree’,‘4’,‘0’))
FROM
Person,
Schooling
WHERE
( Person.ID = Schooling.ID )
AND (
to_number(decode(
Schooling.Degree,‘Associates’,‘1’,‘Bachelors’,‘2’,‘Masters’,‘3’,‘Doctorate’, ‘4’,‘Doctoral Degree’,‘4’,‘0’)) = ALL
(
SELECT
max( to_number(decode(
Schooling.Degree,‘Associates’,‘1’,‘Bachelors’,‘2’,‘Masters’,‘3’,‘Doctorate’, ‘4’,‘Doctoral Degree’,‘4’,‘0’)))
FROM
Schooling
Person Person_Alias
WHERE
Schooling.ID = Person_Alias.ID
AND Person_Alias.Name = Person.Name
)
)

I think the above is technically correct, but… I have a hunch that BO (and maybe even the database) does not support a MAX function with TO_NUMBER.
If you are able to get the Calculation wizard to work with another object that does not have the TO_NUMBER(DECODE(blah, blah, blah)) stuff, then there is nothing wrong with BO, but with the SQL it is trying to generate when that stuff is in there.

Regards,
Luis Gonzalez

Listserv Archives (BOB member since 2002-06-25)