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] compare my trans date to current date

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[SOLVED] compare my trans date to current date
 Login/Join
 
Brenda Wilkerson
Gold member
posted
I set up define:
DEFINE FILE WKFLOW_SUM_FACT
TransactionDate/MDYY=HDATE( WKFLOW_SUM_FACT.WKFLOW_SUM_FACT.DT_KEY ,'YYMD' );
TRAN_YEAR/YY=TransactionDate;
TRAN_MONTH/Mtr=TransactionDate;
TRAN_QTR/YYQ=TransactionDate;
CURDATE/HMDYY=HGETC(10,'HMDYY');
END

Then I set up where/if:
WHERE ( WKFLOW_SUM_FACT.WKFLOW_SUM_FACT.TransactionDate EQ CURDATE )

and get this error:
0 ERROR AT OR NEAR LINE 67 IN PROCEDURE myprocedure
(FOC280) COMPARISON BETWEEN COMPUTATIONAL AND ALPHA VALUES IS NOT ALLOWED
(FOC009) INCOMPLETE REQUEST STATEMENT
BYPASSING TO END OF COMMAND

If I look at both I see them coming in as m/d/yy.

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



WebFOCUS 7.7.03
Windows 7
 
Posts: 55 | Location: USA | Registered: November 02, 2009Report This Post
ERINP
Platinum Member
posted Hide Post
Brenda,
either use a Smart Date to a Smart Date comparison or use Alpha to Alpha comparisons. Within any report you have amper variables available to you for this type of comparison:
 
 &DATE         = 10/05/11
 &DMY          = 051011
 &DMYY         = 05102011
 &MDY          = 100511
 &MDYY         = 10052011
 &YMD          = 111005
 &YYMD         = 20111005

any of these can be used for a smart date to smart date comparison or you could convert them to Alpha and compare. Hope this helps.

ERINP

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


WebFOCUS 7.6.9

Reporting client Windows 2003 Service pack 2 using IIS and TomCat 5.5
Reporting Server OS/400 V5R4M0
Outputs: HTML, Excel, PDF, CSV, and Flat Files
 
Posts: 130 | Location: Columbus, Ohio | Registered: February 25, 2009Report This Post
njsden
Virtuoso
posted Hide Post
Assuming tha TransactionDate is a date-time field (e.g. HYYMDs), why not:
...
WHERE WKFLOW_SUM_FACT.WKFLOW_SUM_FACT.TransactionDate EQ DT(&YYMD 00:00:00)
...

You can then get rid of all those DEFINE'd fields


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
njsden
Virtuoso
posted Hide Post
Of course, it would be easier to help if you told us what ACTUAL and USAGE values are there for TransactionDate in your master file.


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
Brenda Wilkerson
Gold member
posted Hide Post
for report I only want to select records where the transactions date in my master file is = to the current date.
The format in my master file is: HYYMDS (2011/02/14 00:00:00.000)

Is that what you were asking?



WebFOCUS 7.7.03
Windows 7
 
Posts: 55 | Location: USA | Registered: November 02, 2009Report This Post
njsden
Virtuoso
posted Hide Post
Exactly! Didn't this work for you?

WHERE WKFLOW_SUM_FACT.WKFLOW_SUM_FACT.TransactionDate EQ DT(&YYMD 00:00:00)


That should retrieve the records with TransactionDate equals to the current date, which in WebFOCUS can be obtained by reading &YYMD.

The DT() function is there to convert current date (which would give you 20111005 for instance) to a date-time value that can be compared to your database field.


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
Brenda Wilkerson
Gold member
posted Hide Post
this is what i get:
0 ERROR AT OR NEAR LINE 45 IN PROCEDURE dexflow_ca_rater_w
(FOC006) THE FORMAT OF THE TEST VALUE IS INCONSISTENT WITH FIELD FORMAT:
20111005 00:00:00
BYPASSING TO END OF COMMAND
(FOC009) INCOMPLETE REQUEST STATEMENT



WebFOCUS 7.7.03
Windows 7
 
Posts: 55 | Location: USA | Registered: November 02, 2009Report This Post
ERINP
Platinum Member
posted Hide Post
Brenda,
I assume you are only concerned with the date portion of the time stamp?? Otherwise are you ever going to have a transaction that exactly matches the current date and time to the millisecond?
I would HCNVRT the timestamp to get the date portion as an Alpha field and convert it to a smart date:

 
-* In the DEFINE
-* timestamp to alpha
ALPHA_DATE_TIME1/A20 = HCNVRT(Transactiondate, '(H17)', 17, 'A20');
-*strip out the date portion
ALPHA_DT/A8 = EDIT(ALPHA_DATE_TIME1, '99999999$$$$$$$$$$$$'); 
-*convert to a date field
TRANS_DT/YYMD = DATECVT(ALPHA_DT, 'A8YYMD', 'MDYY'); 
-*make todays date EQ to &YYMD
TODAYS_DATE/YYMD = '&YYMD'; 
-*Then compare TRANS_DT to TODAYS_DATE for your WHERE statement


WebFOCUS 7.6.9

Reporting client Windows 2003 Service pack 2 using IIS and TomCat 5.5
Reporting Server OS/400 V5R4M0
Outputs: HTML, Excel, PDF, CSV, and Flat Files
 
Posts: 130 | Location: Columbus, Ohio | Registered: February 25, 2009Report This Post
njsden
Virtuoso
posted Hide Post
Okay ... what happens if you simply say:

WHERE WKFLOW_SUM_FACT.WKFLOW_SUM_FACT.TransactionDate EQ DT(&YYMD)


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
njsden
Virtuoso
posted Hide Post
ERINP, though your approach may work it might cause WebFOCUS to retrieve all of the data to then be filtered internally, as opposed to having the database apply the filters directly (and therefore taking advantage of any available table partitions, indices, etc).

Of course, if the volume of data is not significant this may not be an issue at all but I always try to make the database work harder Wink


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
Brenda Wilkerson
Gold member
posted Hide Post
Bingo Good One



WebFOCUS 7.7.03
Windows 7
 
Posts: 55 | Location: USA | Registered: November 02, 2009Report This Post
ERINP
Platinum Member
posted Hide Post
Brenda,
was njsden solution functional? if it is I will start using that for ALL of my timestamp comparisons. A lot shorter and more direct approach.


ERINP


WebFOCUS 7.6.9

Reporting client Windows 2003 Service pack 2 using IIS and TomCat 5.5
Reporting Server OS/400 V5R4M0
Outputs: HTML, Excel, PDF, CSV, and Flat Files
 
Posts: 130 | Location: Columbus, Ohio | Registered: February 25, 2009Report This Post
njsden
Virtuoso
posted Hide Post
And more efficient too.

You can enable traces to see what SQL statement WebFOCUS generates to query the data.

In most cases, you will find that filters on date fields based on DEFINES are *not* sent to SQL so there will be a performance hit.

Using DT( <constant> ) on the other hand, allows iWay to translate this into a proper WHERE statement in SQL and now the database (and WebFOCUS) are going to query and fetch only the intended records.


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
Brenda Wilkerson
Gold member
posted Hide Post
ERINP, yes, it is a very simple approach and functional.



WebFOCUS 7.7.03
Windows 7
 
Posts: 55 | Location: USA | Registered: November 02, 2009Report This Post
ERINP
Platinum Member
posted Hide Post
I learn something new just about every day on this site. I will have to give this a try to see if I notice any performance differences in some of my reports. I am surprised that the comparison between a timestamp and a date field does not throw any errors.

Thanks,
ERINP


WebFOCUS 7.6.9

Reporting client Windows 2003 Service pack 2 using IIS and TomCat 5.5
Reporting Server OS/400 V5R4M0
Outputs: HTML, Excel, PDF, CSV, and Flat Files
 
Posts: 130 | Location: Columbus, Ohio | Registered: February 25, 2009Report This Post
njsden
Virtuoso
posted Hide Post
quote:
I am surprised that the comparison between a timestamp and a date field does not throw any errors

Such a comparison would always fail but this is not the case as Brenda is comparing a date-time field [ TransactionDate/HYYMDS ] to a date-time constant [ DT(&YYMD) ].

That's the magic of DT(). Smiler


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
ERINP
Platinum Member
posted Hide Post
njsden,
thanks for the insight. dually noted and book marked for future use Nice Thread


WebFOCUS 7.6.9

Reporting client Windows 2003 Service pack 2 using IIS and TomCat 5.5
Reporting Server OS/400 V5R4M0
Outputs: HTML, Excel, PDF, CSV, and Flat Files
 
Posts: 130 | Location: Columbus, Ohio | Registered: February 25, 2009Report This Post
njsden
Virtuoso
posted Hide Post
You're welcome! I just found some interesting stuff related to this matter here.


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
Francis Mariani
Expert
posted Hide Post
I'm getting the D.T.'s just thinking about this!



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
njsden
Virtuoso
posted Hide Post
I had not heard of "delirium tremens" in such a loooong time

Boy, that was refreshing!


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
  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] compare my trans date to current date

Copyright © 1996-2020 Information Builders