Focal Point
[CASE OPENED: IRN] The simplest of simplest: age calculation

This topic can be found at:
http://forums.informationbuilders.com/eve/forums/a/tpc/f/7971057331/m/1137091526

October 16, 2012, 06:38 AM
Peter
[CASE OPENED: IRN] The simplest of simplest: age calculation
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
October 16, 2012, 09:30 AM
Francis Mariani
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
October 16, 2012, 10:06 AM
Tom Flynn
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
October 16, 2012, 11:37 AM
Peter
@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
October 16, 2012, 11:51 AM
Tom Flynn
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
October 16, 2012, 01:50 PM
rfbowley
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
October 16, 2012, 04:34 PM
MAdams1
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
October 16, 2012, 05:17 PM
Venkat-
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
October 17, 2012, 04:41 AM
Peter
@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
October 18, 2012, 03:35 PM
rfbowley
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
October 18, 2012, 04:40 PM
Francis Mariani
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
October 18, 2012, 07:55 PM
Dan Satchell
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
October 19, 2012, 08:47 AM
Peter
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
October 19, 2012, 10:21 AM
Tom Flynn
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
October 22, 2012, 03:45 AM
JAlbarrán
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.
October 22, 2012, 05:05 AM
Peter
@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
October 22, 2012, 09:16 AM
Tony A
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 FOCUS
since 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 
October 29, 2012, 05:39 AM
Peter
@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