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] Ordering the WHERE clause generated by INFO Assist

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[CLOSED] Ordering the WHERE clause generated by INFO Assist
 Login/Join
 
Member
posted
We are connecting to Hive through InfoAssist+ and we’ve found that the order of our WHERE clause is the number one key to efficiency.

The WHERE clause will filter these tables down by the order they appear in the WHERE clause generated by InfoAssist. For example, we have our data in HIVE grouped by Month, so If we first filter on month then we are looking at just one folder. However, if we filter by another value and then by Month, it will go through all of the folders looking for that first filter and then filter down by month.

Select * from table where Month = ‘201807’ and thing = ‘searchForValue’
That works great!

Select * from table where thing = ‘searchForValue’ and Month = ‘201807’
That will go through everything in order to limit the table by ‘searchForValue’ and then it will go through those records limiting things by ‘201807’

Is there any way to ensure that the Month criteria is always evaluated first?

We’ve noticed that InfoAssist will build the SQL of the WHERE clause using user created filters and then the mandatory filters that had been added to the reporting object or the synonym. The next order of the filters in the SQL then looks at the first table and moves on with the second and third table and then it will generate in reverse order. So putting in a filter for ‘searchForValue’ then adding the filter for ‘201807’ will generate WHERE clause in with ‘201807’ then ‘searchForValue’ followed by any mandatory filter (or WHERE Clause from a Reporting Object).

We are looking for a way to always push the Month to the top. We’d like to do this as a mandatory filter because we don’t want a user to be able to generate anything without specifying the month.

Thanks for looking at this!

This message has been edited. Last edited by: FP Mod Chuck,


8202m, Apache Tomcat/8.5.20
 
Posts: 2 | Location: Location: | Registered: April 06, 2018Report This Post
Virtuoso
posted Hide Post
Hi Screen Handle

Welcome to Focal Point! It seems pretty clear that you have done a thorough analysis of this problem. I think your best bet is to open a case with techsupport and see if they have any suggestions on this as well.

There may be someone in FP land that can provide a technique to help as well.

Please edit your profile and provide us information on the release of WF you are using.


Thank you for using Focal Point!

Chuck Wolff - Focal Point Moderator
WebFOCUS 7x and 8x, Windows, Linux All output Formats
 
Posts: 2127 | Location: Customer Support | Registered: April 12, 2005Report This Post
Member
posted Hide Post
Thank you for the quick reply, Chuck.

I updated my profile some.

Edition:    WebFOCUS Enterprise Edition
Product Release:    8.2
Service Pack:    0.2
Package Name:    wf020118a
Release ID:    8202m
Build/GEN Number:    14
Build/GEN Date:    February 1, 2018 3:00:08 PM CST
Application Server:    Apache Tomcat/8.5.20

Thanks!
Screen Handle
 
Posts: 2 | Location: Location: | Registered: April 06, 2018Report 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] Ordering the WHERE clause generated by INFO Assist

Copyright © 1996-2020 Information Builders