Focal Point Banner


As of December 1, 2020, Focal Point is retired and repurposed as a reference repository. We value the wealth of knowledge that's been shared here over the years. You'll continue to have access to this treasure trove of knowledge, for search purposes only.

Join the TIBCO Community
TIBCO Community is a collaborative space for users to share knowledge and support one another in making the best use of TIBCO products and services. There are several TIBCO WebFOCUS resources in the community.

  • From the Home page, select Predict: WebFOCUS to view articles, questions, and trending articles.
  • Select Products from the top navigation bar, scroll, and then select the TIBCO WebFOCUS product page to view product overview, articles, and discussions.
  • Request access to the private WebFOCUS User Group (login required) to network with fellow members.

Former myibi community members should have received an email on 8/3/22 to activate their user accounts to join the community. Check your Spam folder for the email. Please get in touch with us at community@tibco.com for further assistance. Reference the community FAQ to learn more about the community.


Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [CLOSED] Oracle Query rewrite issue with WebFocus-generated aliases

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[CLOSED] Oracle Query rewrite issue with WebFocus-generated aliases
 Login/Join
 
Peter
Gold member
posted
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,


Prod: WebFOCUS 8.0.06 on Win2008/Tomcat7/LDAP Mode
Test: WebFOCUS 8.0.06 on Win2008/Tomcat7/LDAP Mode
 
Posts: 59 | Registered: April 23, 2012Report This Post
Kerry
Expert
posted Hide Post
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


Kerry Zhan
Focal Point Moderator
Information Builders, Inc.
 
Posts: 1948 | Location: New York | Registered: November 16, 2004Report This Post
Peter
Gold member
posted Hide Post
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


Prod: WebFOCUS 8.0.06 on Win2008/Tomcat7/LDAP Mode
Test: WebFOCUS 8.0.06 on Win2008/Tomcat7/LDAP Mode
 
Posts: 59 | Registered: April 23, 2012Report This Post
  Powered by Social Strata  

Read-Only Read-Only Topic

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [CLOSED] Oracle Query rewrite issue with WebFocus-generated aliases

Copyright © 1996-2020 Information Builders