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,

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

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

@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', $

Never suggested putting the code into the Master...

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

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.

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
 

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

@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!!

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.

Also:

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

-TYPE &RUN_DTTM

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');
  

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!!

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,

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.

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

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

@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