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     [SOLVED] Problem with yesterday's date.

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[SOLVED] Problem with yesterday's date.
 Login/Join
 
Prash1983
Gold member
posted
Hi,
I am trying to fetch data from a AIX DB2 table for yesterday's date.


DEFINE FILE XXX
YESTERDAY/YYMD = DATEADD(HDATE(HGETC(10, 'HYYMDm'),'YYMD'), 'D', -1) ;
END

TABLE FILE XXX
PRINT

-----
-----
WHERE HDATE(DATE_FIELD,'YYMD') EQ YESTERDAY
AND
------------------------
END

DATE_FIELD is in 'HYYMDm' format.

Is there anything that looks incorrect in the code? How can I achieve this in any other way? Please help.

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


webFOCUS 7.6.x,
z/OS(Mainframes),
Excel and HTML
 
Posts: 51 | Registered: August 31, 2009Report This Post
Prarie
Virtuoso
posted Hide Post
TODAY/YYMD = '&DATEYYMD';
YESTERDAY/YYMD = TODAY - 1;

NEWDATE/YYMD = HDATE(DATE_FIELD, 'YYMD');

WHERE NEWDATE EQ YESTERDAY

This should work.


In Focus since 1993. WebFOCUS 7.7.03 Win 2003
 
Posts: 1903 | Location: San Antonio | Registered: February 28, 2005Report This Post
Prash1983
Gold member
posted Hide Post
This doen't work Prarie!
Error:

(FOC1400) SQLCODE IS -99999 (HEX: FFFE7961)
: [01S01] [IBM][CLI Driver] CLI0165E Error in row. SQLSTATE=01S01 [5U026]
: [IBM][CLI Driver][DB2/AIX64] SQL0969N There is no message text corresp
: onding to SQL error "-4712" in the message file on this workstation. Th
: e error was returned from module "SQLRW082" with original tokens "SQRR1
: 11". SQLSTATE=5U026
(FOC1407) SQL FETCH CURSOR ERROR.
(FOC1400) SQLCODE IS 1400 (HEX: 00000578)
: SQLCODE IS -99999 (HEX: FFFE7961)
(FOC1407) SQL FETCH CURSOR ERROR. : XXX

Please note that the code works when hard-coded value is passed instead of a variable.


webFOCUS 7.6.x,
z/OS(Mainframes),
Excel and HTML
 
Posts: 51 | Registered: August 31, 2009Report This Post
Francis Mariani
Expert
posted Hide Post
I would never use a DEFINE based WHERE statement - the WHERE does not get passed to SQL, WebFOCUS will return all the data before applying the WHERE.

This is the message you'd get:

FOC2598 - FOCUS IF/WHERE TEST CANNOT BE PASSED TO SQL  : WHERE expression


The right way to do it would be to create a Dialogue Manager variable with yesterday's date (to use Date functions in Dialogue Manager you need to use DATECVT to convert a string to a date and then back to a string) and use yesterday and today as from-to selection criteria. Something like this:

-SET &YESTERDAY = DATECVT((DATEADD((DATECVT(&YYMD, I8YYMD, YYMD)), D, -1)), YYMD, I8YYMD);

TABLE FILE TIME_CNTL
PRINT
*
WHERE UPDATE_TIMESTAMP GE DT('&YESTERDAY 00:00:00.000') AND UPDATE_TIMESTAMP LT DT('&YYMD 00:00:00.000');
END


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
Prarie
Virtuoso
posted Hide Post
Sorry that did not work, we have it working here...but not sure on which databases.

Yes Fransis that is the best way to go...
 
Posts: 1903 | Location: San Antonio | Registered: February 28, 2005Report This Post
Prash1983
Gold member
posted Hide Post
Thanks a lot Francis!
This works!
I will keep that in mind what you said about NOT using "DEFINE based WHERE statement".
Thanks again!!

Thanks Prarie for the help!!


webFOCUS 7.6.x,
z/OS(Mainframes),
Excel and HTML
 
Posts: 51 | Registered: August 31, 2009Report This Post
Waz
Expert
posted Hide Post
I agree with Francis, go with the DT's

Big Grin


Waz...

Prod:WebFOCUS 7.6.10/8.1.04Upgrade:WebFOCUS 8.2.07OS:LinuxOutputs:HTML, PDF, Excel, PPT
In Focus since 1984
Pity the lost knowledge of an old programmer!

 
Posts: 6347 | Location: 33°49'23.0"S, 151°11'41.0"E | Registered: October 31, 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     [SOLVED] Problem with yesterday's date.

Copyright © 1996-2020 Information Builders