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     [CLOSED]Pass reformatted date for aggregation

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[CLOSED]Pass reformatted date for aggregation
 Login/Join
 
j42p11
Silver Member
posted
I have several DB2 tables where the dates are defined as YYMD (usage=YYMD and actual=DATE). When I need to aggregate the data into a different format like MYY or YY for graphing, I get an error message stating that the field has an unsupported data type. Now I have tried converting the date by a define file two ways, field/myy=date_field and field/MYY=DATECVT(date_field,'YYMD','MYY') as well as played with computes and reformatting on the fly, but I get the same result each time.
Yes I do get a nice single select statement and my answer set does get returned, and I could hold the data and perform a second pass. however, I do not get to let the relational engine aggregate the data for me for faster processing. Done anyone know how to get a reformatted date to pass so aggregation can be done using it?

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


WebFocus 7.7.03 - 7703_hotfix - Gen: 284
Dev Studio 7.7.03 - Gen: 06062011
Mainframe Focus 7.7.03
Windows WF Client Server
zOS Reporting server (hub-sub config)
Windows Reporting server
Windows RC Client Server
 
Posts: 30 | Location: Portland, Oregon, USA | Registered: September 11, 2009Report This Post
njsden
Virtuoso
posted Hide Post
DATE conversions are not very well supported by all iWay adapters and won't translate to your SQL engine. I know the iWay adapter for Oracle does nor offer great support on that but think the MS SQL Sesrver one does. Unfortunately I have no idea what your DB2 one supports but I'd dare to say that it does not support those conversion expressions to be translated to SQL and passed to the engine.

My advice: try and create some sort of Calendar dimension table in your database where, for each date you'll have additional attributes such as Month-Year, Year, etc. You can then join your existing tables to that Calendar and sort/group by the new attributes. No DATE conversions from FOCUS to DB are needed and all will be handled by the database directly.


Prod/Dev: WF Server 8008/Win 2008 - WF Client 8008/Win 2008 - Dev. Studio: 8008/Windows 7 - DBMS: Oracle 11g Rel 2
Test: Dev. Studio 8008 /Windows 7 (Local) Output:HTML, EXL2K.
 
Posts: 1533 | Registered: August 12, 2005Report This Post
j42p11
Silver Member
posted Hide Post
Thanks njsden, I though this might be the case for me. I did find some documentation for new features on 7.7.05 that will allow the passing of native database functions through define fields that can solve my problem, if I were at that release level. So at least there is some light on the horizon. I will look into the date table like you suggested as a fix though. Thanks again.


WebFocus 7.7.03 - 7703_hotfix - Gen: 284
Dev Studio 7.7.03 - Gen: 06062011
Mainframe Focus 7.7.03
Windows WF Client Server
zOS Reporting server (hub-sub config)
Windows Reporting server
Windows RC Client Server
 
Posts: 30 | Location: Portland, Oregon, USA | Registered: September 11, 2009Report 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     [CLOSED]Pass reformatted date for aggregation

Copyright © 1996-2020 Information Builders