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,

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.

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

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 ...).

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.

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

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.

[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

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.