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    WebFOCUS/FOCUS Forum on Focal Point     [CASE OPENED: IRN] The simplest of simplest: age calculation

 Go Search Notify Tools
 [CASE OPENED: IRN] The simplest of simplest: age calculation
Gold member
 posted October 16, 2012 06:38 AM
The treatment of dates in WF is driving me in-sane!

A simple define: calculate the age-difference between today's date and a given birthdate in an oracle table.
(Oracle --> birthdate = HYYMDS)

Option1: define birthdate in masterfile as YYMD --> N/A as this would make it impossible to compare the birthdate with other datefields (unless they are all re-defined and that's not an option)
Option2: DATEDIFF with birthdate conversion --> doesn't translate to SQL
Option3: HDIFF with today conversion --> doesn't translate to SQL
Option4: SQL.months_between(today, birthday) --> works, translates to SQL but FOC32605 error in InfoAssist ("non optimizable expression with SQL. syntax")
Option5-xx: Function x, y, z, ... --> doesn't translate to SQL

How, in the name of all that is dear to me, do I do this??

(Note: I've gone through documentation & focalpoint for hours, but haven't found anything with relation to SQL-translation)

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, 2012 IP
Expert
 posted October 16, 2012 09:30 AM Hide Post
What do you mean by
quote:
calculate the date between today's date and a given birthdate in an oracle table
You can only calculate a unit of time between two dates, not a "date".

Francis

Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server

 Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005 IP
Expert
 posted October 16, 2012 10:06 AM Hide Post
Use HDATE to convert your ORACLE datetimestamp COLUMN.

Then, use that column as in the following:

TABLE FILE YOUR_FILE_NAME
PRINT
COLUMN1
COLUMN2
COLUMN3
COMPUTE BIRTH_DATE/YYMD = HDATE(ORACLE_BIRTHDATE, 'YYMD'); NOPRINT
COMPUTE CUR_DATE/YYMD   = '&DATEYYMD.EVAL'; NOPRINT
COMPUTE AGE/I3          = DATEDIF(BIRTH_DATE, CUR_DATE, 'Y');
BY SORT_COLUMN
END
-EXIT

Tom Flynn
WebFOCUS 8.1.05 - PROD/QA
DB2 - AS400 - Mainframe

 Posts: 1972 | Location: Centennial, CO | Registered: January 31, 2006 IP
Gold member
 posted October 16, 2012 11:37 AM Hide Post
@Francis: Post updated
calculate the date --> calculate the age-difference

@Tom: still not translated to SQL:
FOC2566 - DEFINE DF_AGE CANNOT BE CONVERTED TO SQL
FOC2576 - COMBINATION OF PARAMETERS OF DATEDIF CANNOT BE CONVERTED TO SQL
VARIABLE NAME=&&GV_TODAY, PROMPT='Override today''s date:', USAGE=YYMD, DEFAULT='&YYMD', \$
DEFINE DF_DATE_TODAY/YYMD  WITH DATE_ID='&&GV_TODAY'; TITLE='Current day', \$
DEFINE DF_SUPC_DTBTH/YYMD=HDATE(SUPC_DTBTH,'YYMD'); TITLE='Birthdate', \$
DEFINE DF_AGE/I4=DATEDIF(DF_SUPC_DTBTH, DF_DATE_TODAY, 'Y'); TITLE='Age', \$

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, 2012 IP
Expert
 posted October 16, 2012 11:51 AM Hide Post
Never suggested putting the code into the Master...

DEFINE DF_DATE_TODAY/YYMD WITH DATE_ID='&&GV_TODAY.EVAL'; TITLE='Current day', \$

Tom Flynn
WebFOCUS 8.1.05 - PROD/QA
DB2 - AS400 - Mainframe

 Posts: 1972 | Location: Centennial, CO | Registered: January 31, 2006 IP
Platinum Member
 posted October 16, 2012 01:50 PM Hide Post
Maybe I am just not reading in all the details, but dealing with oracle dates in a report is no big deal. You say you have a field called birthdate in HYYMDS format.

In either a define field, or as a compute, in the final output:

TODAY_DATE/HYYMDS = HGETC(10, 'HYYMDS');
AGE/I5 = HDIFF(BIRTHDATE, TODAY_DATE, 'YEAR', AGE)

You do not have to bother with SQL translations, master files, or anything else. Just return the birthdate field from oracle as is and let FOCUS do it's thing.

DATEDIF does not apply to timestamps. It is only for focus formats such as &YYMD.

Robert F. Bowley Jr.
Owner
TaRa Solutions, LLC

In WebFOCUS since 2001

 Posts: 132 | Location: Gadsden, Al | Registered: July 22, 2005 IP
Guru
 posted October 16, 2012 04:34 PM Hide Post
Going a step further, I recently had to group by age groups. Here is what I did. I had to get the age at the time of application.

DEFINE FILE STUDENT_APPL_HISTORY_DIM
CURRENTDT/YYMD=&YYMD;
BDAY_YYMD/YYMD=HDATE(J0.PEOPLE_DIM.BIRTH_DATE, 'YYMD');
APP_DATE_YYMD/YYMD=HDATE(STUDENT_APPL_HISTORY_DIM.STUDENT_APPL_HISTORY_DIM.APPLICATION_DATE, 'YYMD');
AGE/I3=
IF J0.PEOPLE_DIM.BIRTH_DATE IS MISSING THEN 0
ELSE DATEDIF(BDAY_YYMD, APP_DATE, 'Y');
AGE_GROUPS/A25V=
IF AGE GT 0 AND AGE LE 18 THEN '18 and Under'
ELSE IF AGE GE 19 AND AGE LE 24 THEN '19 to 24'
ELSE IF AGE GE 25 AND AGE LE 30 THEN '25 to 30'
ELSE IF AGE GE 31 AND AGE LE 40 THEN '31 to 40'
ELSE IF AGE GE 41 AND AGE LE 50 THEN '41 to 50'
ELSE IF AGE GT 50 THEN 'Over 50'
ELSE 'Unknown Age';
END

WebFOCUS Server 8.1.05
Windows 2008 Server
WebFOCUS AppStudio 8.1.05
Windows 7 Professional
IE 11 and Chrome Version 43.0.2357.124 m.
Mostly HTML, PDF, Excel, and AHTML

 Posts: 272 | Location: Kalamazoo, Michigan | Registered: September 30, 2010 IP
Silver Member
 posted October 16, 2012 05:17 PM Hide Post
Just an another way for calculating age using simple define:-

DEFINE FILE HOLD_AGE
TODAY_DATE/YYMD=&YYMD;
ACTUAL_DOB/YYMD=J3.PERSON_BIRTHDATE_TBL.BIRTHDATE;
PRESENT_AGE/I3=(TODAY_DATE - ACTUAL_DOB)/365.25;
END

product release:8203
o/s: windows 10
expected o/p formats: HTML,EXCEL,PDF

 Posts: 34 | Location: Kearney, Nebraska | Registered: December 14, 2011 IP
Gold member
 posted October 17, 2012 04:41 AM Hide Post
@Tom: Should have mentioned I was using business views to allow IA-users to simply use pre-defined calculations.
.EVAL results in (FOC177) INVALID DATE CONSTANT
@rfbowley: HGETC results in FOC2576 - COMBINATION OF PARAMETERS OF HDIFF CANNOT BE CONVERTED TO SQL
@MAdams1: DATEDIF results in FOC2576 - COMBINATION OF PARAMETERS OF DATEDIF CANNOT BE CONVERTED TO SQL
@Venkat-: ACTUAL_DOB=Birthdate results in (FOC282) RESULT OF EXPRESSION IS NOT COMPATIBLE WITH THE FORMAT OF FIELD
using the function HDATE to translate birthdate into a YYMD, it works - it's translated to SQL!!!

The two questions I end up with:
- Why does it refuse to translate to SQL with HDIFF & DATEDIFF (gathering from your comments this should happen right?)
- why doesn't this translate to SQL: (DF_DATE_TODAY - HDATE(SUPC_DTBTH,'YYMD')) / 365.25
whereas this does: (DF_DATE_TODAY - DF_SUPC_DTBTH) / 365.25
with DF_SUPC_DTBTH = HDATE(SUPC_DTBTH,'YYMD')

I'll open a case for the outstanding questions...

Thanks for the feedback!!

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, 2012 IP
Platinum Member
 posted October 18, 2012 03:35 PM Hide Post
Well, I think you misunderstood. Do not try to pass the HGETC to SQL, use it after the sql pull, as in:

SQL
SELECT
field1
,field2
,BIRTH_DATE
FROM
table 1
;
TABLE
ON TABLE HOLD AS STEP01
END
-RUN

DEFINE FILE STEP01
TODAY_DATE/HYYMDS = HGETC(10, 'HYYMDS');
AGE/I5 = HDIFF(BIRTHDATE, TODAY_DATE, 'YEAR', AGE)
END

TABLE FILE STEP01
PRINT
AGE
field1
field2
END

Trust me, the challenge is NOT date calculations in FOCUS, it is trying to figure out how to send a valid date format to what ever data base my current client is using.

Now if you ABSOLUTELY HAVE TO send today's date back to have SQL calculate the age, although I can not for the life of me understand why you would want to, try something along these lines.

-SET &Today_Date = EDIT(&YYMD, '9999\$\$\$\$') || '-'  || EDIT(&YYMD, '\$\$\$\$99\$\$) || '-'  ||  EDIT(&YYMD, '\$\$\$\$\$\$99) | ' 00:00:00.0000';

This creates an alpha version of the current date that (db2) SQL can use.

But you are doomed to failure trying to send FOCUS formated dates to any database via SQL without converting to an alpha format that your particular version of SQL will accept.

Robert F. Bowley Jr.
Owner
TaRa Solutions, LLC

In WebFOCUS since 2001

 Posts: 132 | Location: Gadsden, Al | Registered: July 22, 2005 IP
Expert
 posted October 18, 2012 04:40 PM Hide Post
Also:

-SET &RUN_DTTM  = EDIT(&YYMD,'9999-99-99') |' ' | EDIT(HHMMSS('A8'),'99\$:99\$:99') | '.000';

-TYPE &RUN_DTTM

Francis

Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server

 Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005 IP
Virtuoso
 posted October 18, 2012 07:55 PM Hide Post
Another possible variation on acquiring the current date and time in alpha format, depending on whether slashes (/) are acceptable as delimiters for the date, instead of dashes (-):

-SET &CURR_DTTM = HCNVRT(HGETC(10,'HYYMDs'),'(HYYMDs)',23,'A23');

WebFOCUS 7.7.05

 Posts: 1213 | Location: Seattle, Washington - USA | Registered: October 22, 2007 IP
Gold member
 posted October 19, 2012 08:47 AM Hide Post
I feel like I should explain the underlying idea a bit;
What I'm trying to achieve is to provide our business users access via InfoAssist to pre-defined Business views that contain sets of business-translated fields & calculated fields.
The underlying goal being to avoid business users needing to create defines in InfoAssist for common things like age, age-slices, group-values, ...

Most of the examples given are valid for creating defines in reports (regardless via InfoAssist or DevStudio).
Considering the goal of the business views is however to 'cloak' this complexity, I need to do this within the Master file.
As a result, I don't have the luxury of using a multi-step approach (i.e.: first getting fields from database, then performing calculations).

This is also why I'm trying to have all my defines in the business view translate to SQL: as soon as one of the defines doesn't translate to SQL, all of my other defines aren't sent to the database either. (or so I've noticed in my tests)

Am I going completely off the deep end here?
I don't mind being told I've completely misunderstood, but I've done the same type of approach for both OBIEE and BO
(which doesn't mean I've understood how it should work in WebFocus...)

Thanks for the feedback!!

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, 2012 IP
Expert
 posted October 19, 2012 10:21 AM Hide Post
Try this, LONG way; may need to continue using WITH DB_COLUMN_NAME, i.e., WITH DATE_ID:

DEFINE XDATE/A10 WITH DATE_ID = TODAY(XDATE); TITLE='Current day MDYY', \$
DEFINE YDATE/A10              = EDIT(XDATE,'\$\$\$\$\$\$9999/') || EDIT(XDATE,'99999'); TITLE='Current day YYMD', \$
DEFINE DATEX/A8               = EDIT(YDATE,'9999\$99\$99');
DEFINE DATEY/I8YYMD           = EDIT(DATEX);
DEFINE DATEZ/YYMD             = DATEY;
DEFINE DF_SUPC_DTBTH/YYMD     = HDATE(SUPC_DTBTH,'YYMD'); TITLE='Birthdate', \$
DEFINE DF_AGE/I4              = DATEDIF(DF_SUPC_DTBTH, DATEZ, 'Y'); TITLE='Age', \$

EDIT: Incorrect Birthday column...

This message has been edited. Last edited by: Tom Flynn,

Tom Flynn
WebFOCUS 8.1.05 - PROD/QA
DB2 - AS400 - Mainframe

 Posts: 1972 | Location: Centennial, CO | Registered: January 31, 2006 IP
Member
 posted October 22, 2012 03:45 AM Hide Post
Peter, about the FOC32605 in IA when you use SQL functions, it fails on the preview but not on the actual run of the report.
It probably has something to do with the way the preview is picking up the set of data.
I suppose is not your desire, but as a workaround for this option you could just hide that preview on IA by selecting the design Query instead of Interactive.

 Posts: 8 | Location: Spain | Registered: November 05, 2010 IP
Gold member
 posted October 22, 2012 05:05 AM Hide Post
@Tom: Tried your code, but still FOC2576 - even when just testing:
DEFINE DF_SUPC_DTBTH/YYMD     = HDATE(SUPC_DTBTH,'YYMD'); TITLE='Birthdate', \$
DEFINE DF_AGE/I4              = DATEDIF(DF_SUPC_DTBTH, DF_SUPC_DTBTH, 'Y'); TITLE='Age', \$

@JAlbarrán: I noticed it still ran, but considering my previous comment on the use of IA by business users, it's not something I want to confront my users with... (I don't even want to confront them with SQL. to be frank)
Note: it seems this can be 'resolved' in WF8.02 by using "-SET &&IA_PREVIEW_OPT='NO';", but I couldn't get this to work in WF7.7 (see case 82402507)

... kind of disappointed in WF here ...

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, 2012 IP
Expert
 posted October 22, 2012 09:16 AM Hide Post
Peter,

I am sure that I read somewhere in the above postings that you cannot afford to have the date time field specified as a date field as it could be utilised elsewhere (or similar wording), but there is no reason why you shouldn't have a seperate field pointing to the same source column. The main requirement for an RDBMS synonym is that the ALIAS reflects the actual column name in the source table. What I will often suggest to my Clients is to add that addtional field for specific purposes -
FIELDNAME=SUPC_DTBTH, ALIAS=SUPC_DTBTH, USAGE=HYYMDS, ACTUAL=HYYMDS, TITLE='Birth Date/Time', \$
FIELDNAME=SUPC_DTBTH_DT, ALIAS=SUPC_DTBTH, USAGE=YYMD, ACTUAL=DATE, TITLE='Birth Date', \$

However, your signature shows that you are on 7.7.03m so your luck is in. There is a new function within 7.7.03 which allows you to use an RDBMS function (vendor or user supplied) within a DEFINE or WHERE. Unfortunately I do not have a link for the documentation that I can give you, but the syntax is basically -
DEFINE FILE EMPLOYEE
CUMNUM/I8 = SQL.CSUM(EDLEV,JOBTITLE);
END
TABLE FILE EMPLOYEE
PRINT JOBTITLE EDLEV CUMNUM
END

I would suggest a search on "Direct RDBMS Function Calls" in the advanced search under technical support.

T

 In FOCUSsince 1986 WebFOCUS Server 8.2.01M, thru 8.2.07 on Windows Svr 2008 R2 WebFOCUS App Studio 8.2.06 standalone on Windows 10

 Posts: 5694 | Location: United Kingdom | Registered: April 08, 2004 IP
Gold member
 posted October 29, 2012 05:39 AM Hide Post
@Tony: thanks for the tip - I've actually started applying this and it does prevent quite a few issues! (Good advice!)
Regarding SQL.: see my comments above on IA-preview & Business user acceptance...

Otherwise, a quick summary and a (final) closing comment

- a lot of (date/datetime) functions in WF aren't translated into Oracle-SQL even though the same functions exist in Oracle
- a lot of solutions exist in fex-reports, but most don't work in master files
- above two problems combined cause for an issue with self-service via InfoAssist (based on master files)
--> Case opened with IRN attached + sales rep contacted
- a specific workaround for birthdates in master files was:
VARIABLE NAME=&&GV_TODAY, PROMPT='Override today''s date:', USAGE=YYMD, DEFAULT='&YYMD', \$
DEFINE DF_DATE_TODAY/YYMD WITH DATE_ID='&&GV_TODAY'; TITLE='Current day', \$
DEFINE DF_SUPC_DTBTH/YYMD=HDATE(SUPC_DTBTH,'YYMD'); TITLE='Birthdate',
DEFINE DF_AGE/I2=(DF_DATE_TODAY - DF_SUPC_DTBTH)/365.25; TITLE='Age',
(the variable was just a trick to allow to simulate reports that were executed in the past)

Should something else come out of discussions with my sales rep or the case in question, I'll update this post

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, 2012 IP