Hi all, Can anyone of you sugges

[sreenathkm]

Hi all,

Can anyone of you suggest a way where in I can select a particular range of records with a particular status.Mean to say my fact table with around 330 Million records have statuses like T1,T2,T3...T10.Now the issue is users will be interested to see only one out of this 10 statuses for each ref_no.on the priority that T1 stand first if not found T2 should appear with the corresponding values if T2 not found T3 should appear.....

For example my records are like

Ref_no Status Amount
1001 T1 100
1001 T3 200
1001 T7 -40
..
..

In this example my report should fetch values pertaining to T1 only

This data retrieval will be a result of join with 2 more fact tables which are of volume more than 100M records.I tried to do the following:

select x,x,x,x,max(decode(a.status,'T1',10,'T2',9,'T3',8....) from table
group by a.status

as I mentioned earlier because of the volume of data my 'group by' is never returning(in spite of all my optmising efforts with all possible oracle hints) more over I am not sure if I can use a group by in BO.

Any suggestions Please

Thanks in advance
sreenathkm@hotmail.com

 

[blom0344]

How about creating a view or materialized view that stores only the highest priority records based on Status for each Ref_no:

Select Ref_no, 'T'||(to_char(min(to_number(substr(Status,1,1))))) as Stat2,'LINK' from table group by Ref_no

Use this as a second dataprovider, link the dataproviders over the dimensions Ref_no and status and use the dummy field 'LINK' (as measure) to show only the available highest priority records (apply a filter on it)

And yes, BO does accept the group by , cause it is mandatory with aggr. functions like sum,count,min etc. T. Blom
Information analyst
tbl@shimano-eu.com