Hi,

The problem described below is not a WebFocus problem, but rather an Oracle problem.
The question is open with Oracle support, but I was wondering if others have had the same issue trying to use the oracle aggregate awareness... (is there anyone out there?)

2 materialized views exist (one agg per day, one agg per month) and 1 dimension exists for time (date - month - quarter - year)

Query rewrite seems to be working fine for a query as such (alias sql = alias mv):

 select DDFPC.MONTH_NO 
from BIDATA_MAIN.DF_REPOSITORY_SUBSET DF2, BIDATA_MAIN.DD_TIME DDFPC 
where DF2.FINANCIAL_PROCCESSING_DATE_ID = DDFPC.DATE_ID and DF2.ROUTING_CODE_ISS IN ('FPC','NCC') 
group by DDFPC.MONTH_NO 

However, considering webfocus generates aliases on the fly, we could get the following query (alias sql <> alias mv):

 select T2.MONTH_NO 
from BIDATA_MAIN.DF_REPOSITORY_SUBSET DF2, BIDATA_MAIN.DD_TIME T2
where DF2.FINANCIAL_PROCCESSING_DATE_ID = T2.DATE_ID and DF2.ROUTING_CODE_ISS IN ('FPC','NCC') 
group by T2.MONTH_NO 

--> for the second query, query rewrite refuses to correctly use the dimension – it’ll rewrite to the DD (daily) aggregate, but not to the MM (monthly) aggregate
Investigation gives: QSM-01072: materialized view, DA_REPOSITORY_MERCHANT_MM, and query have different joins between tables, DF_REPOSITORY_SUBSET and DD_TIME

This message has been edited. Last edited by: Peter,

Hi Peter,

Since, as stated, this is more of an Oracle problem, you may want to contact Oracle support for more info, or maybe another Oracle forum? If you have an update/solution, please kindly keep us posted. Thank you in advance for sharing with all.

Cheers,

Kerry

It seems the explanation is quite simple: it's a bug in Oracle that needs squishing.

Given we've got an Oracle 11 upgrade in the pipeline anyway, this should resolve the issue.

@Kerry: the issue itself was indeed posted in Oracle forums and introduced to Oracle support, but given IB advises its clients to make use of the database aggregation features, I was kind of expecting I wouldn't be the first WF-user to run in to this type of issue.

onwards! squish squish squish