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     [SOLVED] Filter synonym using date functions in join

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[SOLVED] Filter synonym using date functions in join
 Login/Join
 
TeePee
Member
posted
I'm trying to limit the date returned in my master file by linking to a date table and then calculating the incident date and today's date.

I was able to make my join using the following function: JOIN_WHERE = HDATE(TT_ACCID_DT, 'YYMD') EQ CALENDAR_DATE

In a report I am able to calculate the months between today's date and this TT_ACCID_DT field:
COMPUTE MTHBTWN/D12.2 = HDIFF(HGETC(8, 'HYYMD'), TT_ACCID_DT, 'MONTH', 'D12.2');
and it will return the months between the accident date and today's date.

When I add this function to my join in the master file: AND HDIFF(HGETC(8, 'HYYMD'), TT_ACCID_DT, 'MONTH', 'D12.2') GE 40
I can not get the report to return any data, it just says 'Waiting for response...' and when I try to see what the sample data is with parent key, all of my joined fields are 0 (zero).

Does anyone have a suggestion how I can limit my data to accidents dates that are 42 mths in the past or greater?

Thanks,
TP

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


WF 8.0.08
 
Posts: 19 | Location: Calgary, AB | Registered: July 09, 2014Report This Post
atturhari
Platinum Member
posted Hide Post
I would first start with a DEFINE for (HDIFF(HGETC(8, 'HYYMD'), TT_ACCID_DT, 'MONTH', 'D12.2')) and see what value it returns. Then, use the DEFINE in conditional join.
 
Posts: 165 | Registered: September 29, 2008Report This Post
TeePee
Member
posted Hide Post
I have now tried this approach but no luck.

When I hard code a condition of Year GE 2011 (ie. 48 mths of data), the report returns with data, including my MonthsBetween field, in about 15 sec.

When I add in a condition where MonthsBetween GE 40, the report says 'Waiting for response...' forever and then never returns data.

Any other suggestions?

Thanks,
TP


WF 8.0.08
 
Posts: 19 | Location: Calgary, AB | Registered: July 09, 2014Report This Post
j.gross
Virtuoso
posted Hide Post
1. I assume you are pulling data from a relational database. If you set XRETRIEV to OFF (so that the request is not issued to the server), and get a statement trace, does the SQL look sensible?

2. Consider moving the condition out of the JOIN, and into the report (TABLE ... WHERE ...).


- Jack Gross
WF through 8.1.05
 
Posts: 1925 | Location: NYC | In FOCUS since 1983 | Registered: January 11, 2005Report This Post
atturhari
Platinum Member
posted Hide Post
quote:
When I hard code a condition of Year GE 2011 (ie. 48 mths of data), the report returns with data, including my MonthsBetween field, in about 15 sec.


Since it took 15 sec, i suspect the filter condition of Year GE 2011 is NOT being passed to the database.
When the filtering does not happen at the database, the WF server will handle it and with large volume of data there are chances of running into an issue like this.

As j.gross suggested, get the SQL traces and optimize your WF query to pass the filters to the database.

To extract YEAR component from a date field, use DPART function.
 
Posts: 165 | Registered: September 29, 2008Report This Post
TeePee
Member
posted Hide Post
When I try to use the HDIFF function in my where clause, I get the following error. This occurs whether the clause in on my join in the master file or in a report.

(FOC1517) UNRECOGNIZED COMMAND DISPLAYS SQL CODE BEING SENT TO DATABASE
08.29.23 BT (FOC2565) THE OBJECT OF IF/WHERE CANNOT BE CONVERTED TO SQL
08.29.23 BT (FOC2566) DEFINE MTHSBTWN CANNOT BE CONVERTED TO SQL
08.29.23 BT (FOC2576) COMBINATION OF PARAMETERS OF FUNCTION HDIFF CANNOT BE CO
08.29.23 BT (FOC2590) AGGREGATION NOT DONE FOR THE FOLLOWING REASON:
08.29.23 BT (FOC2594) AGGREGATION IS NOT APPLICABLE TO THE VERB USED

Does anyone have any other ideas how I can limit my recordset, in the master file, to only include 40 mths? I would be using the accident date field for evaluation.

Thanks,
Trevor


WF 8.0.08
 
Posts: 19 | Location: Calgary, AB | Registered: July 09, 2014Report This Post
j.gross
Virtuoso
posted Hide Post
If your report is to select instances where values of a "date" (H format) field lie within an interval relative to the run date, you should be able to set up amper vars for the two end-dates, and use them in a WHERE condition like
where 
that_date ge dt(&startdate 00:00:00) 
and
that_date le dt(&enddate 23:59:59) 
;


and that should be translatable to SQL.

You can readily use that predicate either in the JOIN or in the TABLE part of the fex; trying to use that approach in JOIN_WHERE in the mfd is possible, but complex and IMHO inadvisable.


- Jack Gross
WF through 8.1.05
 
Posts: 1925 | Location: NYC | In FOCUS since 1983 | Registered: January 11, 2005Report This Post
TeePee
Member
posted Hide Post
[1] Using a technique that Dirk K. showed me, I created a procedure (Get_Today.fex) and then included this in the MFD_PROFILE statement.
-* File Get_Today.fex
-SET &&STARTDATE = DATECVT(DATEADD(DATECVT(&YYMD, 'I8YYMD', 'YYMD'), 'M', -40), 'YYMD', 'A8YYMD');

[2] I created a global variable called &&STARTDATE in my synonym
VARIABLE NAME=&&STARTDATE, USAGE=HYYMDs, DEFAULT='2014/01/01', $

[3] I created a condition in my join to include only accident dates that were greater than or equal to this start date.
JOIN_WHERE=HDATE( TT_ACCID_DT, 'YYMD' ) EQ CALENDAR_DATE
AND CALENDAR_YEAR GE 2011
AND HDATE( TT_ACCID_DT, 'YYMD' ) GE &&STARTDATE;, $

When I create a report using this synonym and no other where conditions, my date is limited to only those accident dates that are GE my start date.

Thanks to all for their assistance.
TP


WF 8.0.08
 
Posts: 19 | Location: Calgary, AB | Registered: July 09, 2014Report This Post
j.gross
Virtuoso
posted Hide Post
Your [1], [2], and [3] is what I was alluding to by "possible, but complex". If you need to imbed that date condition in the MFD, that how.


- Jack Gross
WF through 8.1.05
 
Posts: 1925 | Location: NYC | In FOCUS since 1983 | Registered: January 11, 2005Report 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     [SOLVED] Filter synonym using date functions in join

Copyright © 1996-2020 Information Builders