Complex SQL query in BO

[patrickw777]

Dear Gurus,

I'm trying to filter out rows from a table using a max(seq_num) grouped by an ID. Is there a way to do this in BusinessObjects (Universe and or report) without building an aggregate table in the database?

Here's the query I need to emulate with BO:
--------------------------
select ah.case_id, ah.eventname, ah.seq_num from action_hist ah,
(select max(seq_num) as max_seq, case_id from action_hist group by case_id) maxresults
where ah.case_id = maxresults.case_id
AND
ah.seq_num = max_seq order by ah.case_id;
---------------------------------------

Thanks,
Patrick

 

[Garethe]

You could use a subquery in the where clause - either create a condition in the universe:

WHERE:
ah.seq_num = (select max(maxresults.seq_num)
from action_hist maxresults
where ah.case_id = maxresults.case_id
group by maxresults.case_id)

Add it to your query (containing the ah.case_id, ah.eventname, ah.seq_num objects)

Or, add the same statement to the WHERE clause of an object that you want to return the max(seqnum):
SELECT:
ah.seq_num
WHERE:
ah.seq_num = (select max(maxresults.seq_num)
from action_hist maxresults
where ah.case_id = maxresults.case_id
group by maxresults.case_id)

Hope this helps.

 

[sureshibm]

you can create a user defined object in that class with the requred selection text.

you can put your sql query in the select part of that object and select it in report it will work.

(select max(seq_num) as max_seq, case_id from action_hist group by case_id)

Cheers... SureshLella

--sureshhh

 

[Sridharan]

Patrick,

What you are trying to do is Correlated Subquery and well you can do it as Garethe has told by creating objects in the universe. But the second solution won't work for you as you need to get the Max Seq Num for each Case ID... Other way of accomplishing it is while creating the reports. While putting the condition choose Calculation as the Operand and a Wizard should take you thru further. All you have to do is select Seq_Num and choose Max as the function and choose it evaluate it for each Case ID...

Sri