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     Timestamp getting invalid length error

Read-Only Read-Only Topic
Go
Search
Notify
Tools
Timestamp getting invalid length error
 Login/Join
 
sbp04
Member
posted
I created an MFD that has a field that is a timestamp in DB2, but is set to HYYMDm format in the MFD. When I try to create a report in painter and use the timestamp field in my where clause it says that the value is invalid length. It's like the MFD format and the DB2 format don't match? I was thinking about changing the MFD's field format to A26, does anyone know if this will work?


sbp
 
Posts: 10 | Registered: December 26, 2006Report This Post
BlueZone
Platinum Member
posted Hide Post
sbp ;
Changing the Timestamp field format to A26 in the MFD will work just fine. We do it all the time.

There is only one catch though (as far as I have seen so far), you may have issues if you use a lot of SQL-passthru in your code. TS fields put to a hold file via SQL-passthru retain the HYYMDm format. If you ever have to do a FOCUS join to a TS field defined as A26, you need to make sure to CHAR() the TS from the SQL passthru.

Here is a previous thread on this topic -
https://forums.informationbuilders.com/eve/forums/a/tpc/...1057331/m/2881012902

Hope that helps.
Sandeep Mamidenna


-------------------------------------------------------------------------------------------------
Blue Cross & Blue Shield of MS
WF.76-10 on (WS2003 + WebSphere) / EDA on z/OS + DB2 + MS-SQL
MRE, BID, Dev. Studio, Self-Service apps & a dash of fun !! Music
 
Posts: 218 | Location: Jackson, MS | Registered: October 31, 2006Report This Post
Francis Mariani
Expert
posted Hide Post
TABLE FILE BASEL_TIME_MONTH_CNTL
PRINT
UPDATE_TIMESTAMP
WHERE UPDATE_TIMESTAMP EQ DT(20070208 12:47:15.729309) 
END


UPDATE_TIMESTAMP is defined in the Master as this:

FIELD=UPDATE_TIMESTAMP, UPDATE_TIMESTAMP, HYYMDm, HYYMDm, MISSING=ON, $


You must use the DT function in the WHERE statement.


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, 2005Report This Post
sbp04
Member
posted Hide Post
The way our tables format timestamp fields is '2007-01-01-00.00.00.000000'. Will the DT() in the where clause accept the value like that?


sbp
 
Posts: 10 | Registered: December 26, 2006Report This Post
Francis Mariani
Expert
posted Hide Post
sbp, the example I provided is a working example.

The value must be coded exactly like I show - no dashes and no apostrophes, it's treated as a numeric field.


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, 2005Report This Post
sbp04
Member
posted Hide Post
The report ran good using the DT function. Thanks for the help wit that Francis. However it displays the date like you mentioned in your example. However I would like it to display like it is on our tables. If this is already set as HYYMDm then how would I convert the datetime format to '2007-01-01-00.00.00.000000'?


sbp
 
Posts: 10 | Registered: December 26, 2006Report This Post
Francis Mariani
Expert
posted Hide Post
TABLE FILE BASEL_TIME_MONTH_CNTL
PRINT
UPDATE_TIMESTAMP
COMPUTE UPDATE_TIMESTAMPA/A26 = HCNVRT (UPDATE_TIMESTAMP,'(HYYMDm)', 26, 'A26');
COMPUTE UPDATE_TIMESTAMPB/A26 = EDIT(UPDATE_TIMESTAMPA,'9999$-99$-999999999999999999');
WHERE UPDATE_TIMESTAMP EQ DT(20070111 08:51:36.921850)
END

Unfortunately, as far as I know, you have to use HCNVRT and then EDIT to replace the / by -.

Don't filter on the COMPUTE or DEFINE fields - that does not get passed to SQL.


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, 2005Report This Post
sbp04
Member
posted Hide Post
Thanks, Francis! That worked perfectly!


sbp
 
Posts: 10 | Registered: December 26, 2006Report 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     Timestamp getting invalid length error

Copyright © 1996-2020 Information Builders