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) How to compare 2 days effectively (without pulling the whole table from DB)?

Read-Only Read-Only Topic
Go
Search
Notify
Tools
(Solved) How to compare 2 days effectively (without pulling the whole table from DB)?
 Login/Join
 
bug
Platinum Member
posted
Hi,

I'm trying to run a query to retrieve all records where date1 is certain days later than date2. Tried 2 method. In both ways, webfocus will pull the whole table down first, then compare them in the result set.

1. use define:

DEFINE FILE TBLXXX
DIFF/I4=HDIFF(DATE1,DATE2,'dd',DIFF);
END

TABLE FILE TBLXXX
PRINT DATE1 DATE2
WHERE DIFF GT 10
END

2. use HDIFF in WHERE clause:

TABLE FILE TBLXXX
PRINT DATE1 DATE2
WHERE HDIFF(DATE1,DATE2,'dd','I4') GT 10;
END

When I turn TRACE on, both execute the same SQL to pull over 1 million records down first:

16.35.43 BR (FOC2598) FOCUS IF/WHERE TEST CANNOT BE PASSED TO SQL : WHERE exp
16.35.43 BR (FOC2590) AGGREGATION NOT DONE FOR THE FOLLOWING REASON:
16.35.43 BR (FOC2594) AGGREGATION IS NOT APPLICABLE TO THE VERB USED
16.35.43 AE SELECT T1."DATE1",T1."DATE2" FROM
16.35.43 AE SCHEMA.TBLXXX T1;

Is there a better way to let it compare in DB first?

My DB is Oracle, but if there is a way to do it, it should be DB independent right?

Thanks.

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


7.66 and 7.704
System: Windows / AIX / Linux
Output: Mostly HTML, with some PDF, Excel and Lotus(!)
 
Posts: 147 | Location: Toronto (GTA) | Registered: May 25, 2005Report This Post
Dan Satchell
Virtuoso
posted Hide Post
You might try this.

TABLE FILE TBLXXX
 PRINT DATE1 DATE2
 WHERE ( DATE1 - DATE2 ) GT 10 ;
END


WebFOCUS 7.7.05
 
Posts: 1213 | Location: Seattle, Washington - USA | Registered: October 22, 2007Report This Post
bug
Platinum Member
posted Hide Post
Dan, that solution doesn't work. Because DATE1 and DATE2 are DATETIME STAMP format (HYYMDS).

(FOC281) ALPHA ARGUMENTS IN PLACE WHERE NUMERIC ARE CALLED FOR
(FOC009) INCOMPLETE REQUEST STATEMENT


7.66 and 7.704
System: Windows / AIX / Linux
Output: Mostly HTML, with some PDF, Excel and Lotus(!)
 
Posts: 147 | Location: Toronto (GTA) | Registered: May 25, 2005Report This Post
Prarie
Virtuoso
posted Hide Post
Then change the format of the dates.

DDATE/YYMD = HDATE(DATE1, 'YYMD');


In Focus since 1993. WebFOCUS 7.7.03 Win 2003
 
Posts: 1903 | Location: San Antonio | Registered: February 28, 2005Report This Post
Danny-SRL
Virtuoso
posted Hide Post
Did you try to describe your dates in the MASTER with USAGE=YYMD, ACTUAL=DATE ?


Daniel
In Focus since 1982
wf 8.202M/Win10/IIS/SSA - WrapApp Front End for WF

 
Posts: 1980 | Location: Tel Aviv, Israel | Registered: March 23, 2006Report This Post
Dan Satchell
Virtuoso
posted Hide Post
Have you tried converting your dates to Smart Dates? I don't know if the SQL translator can translate HDATE or not. If not, maybe it cannot translate any of the DATETIME functions. In that case, the only solution may be to use pass-thru SQL and write the SQL yourself.

DEFINE FILE TBLXX
 DATE1X/YYMD = HDATE(DATE1,'YYMD');
 DATE2X/YYMD = HDATE(DATE2,'YYMD');
END
-*
TABLE FILE TBLXXX
 PRINT DATE1 DATE2
 WHERE ( DATE1X - DATE2X ) GT 10 ;
END


WebFOCUS 7.7.05
 
Posts: 1213 | Location: Seattle, Washington - USA | Registered: October 22, 2007Report This Post
njsden
Virtuoso
posted Hide Post
Dan, I have faced this situation before and no, the SQL translator to Oracle adapter cannot resolve HDATE or any other date function for that matter.

Danny's suggestion is the way to go!

As one may probably still need to "see" Oracle date fields as TIMESTAMPs (since that's what they are anyway), one approach I usually use when hitting cases like this is to manually add an extra field declaration to the masterfile referencing the same database column but using of course a different name; that virtual field would have USAGE=YYMD, ACTUAL=DATE as pointed by Danny.

To illustrate the idea, here's a sample masterfile as originally created on an Oracle table:
FILENAME=MYTABLE, SUFFIX=SQLORA  , $
SEGMENT=MYTABLE, SEGTYPE=S0, $
FIELDNAME=ID, ALIAS=ID, USAGE=P11, ACTUAL=P6, $
FIELDNAME=CREATE_DT, ALIAS=CREATE_DT, USAGE=HYYMDS, ACTUAL=HYYMDS, MISSING=ON, $


After manually modifying it I now have access to a "new" CREATE_DT_YYMD field which can be used on WHERE and BY statements handled directly by the database instead of by WebFOCUS.

Here's the new masterfile version:
FILENAME=MYTABLE, SUFFIX=SQLORA  , $
SEGMENT=MYTABLE, SEGTYPE=S0, $
FIELDNAME=ID, ALIAS=ID, USAGE=P11, ACTUAL=P6, $
FIELDNAME=CREATE_DT, ALIAS=CREATE_DT, USAGE=HYYMDS, ACTUAL=HYYMDS, MISSING=ON, $
FIELDNAME=CREATE_DT_YYMD, ALIAS=CREATE_DT, USAGE=YYMD, ACTUAL=DATE, MISSING=ON, $


Hope that helps,
- Neftali.


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
Complementing on the previous post, it should be obvious that you can still use CREATE_DT in those cases where the use of a TIMESTAMP is required.

The best of both worlds and both of them handled directly by the database engine!

- Neftali.


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
Dan Satchell
Virtuoso
posted Hide Post
A note of caution: manually altering the master can certainly provide viable solutions - as long as anyone using the synonym generator to refresh or re-create the master knows and remembers to add these manual entries to the master aferwards.


WebFOCUS 7.7.05
 
Posts: 1213 | Location: Seattle, Washington - USA | Registered: October 22, 2007Report This Post
bug
Platinum Member
posted Hide Post
Neftali,

I tried your solution and it works just as what I wanted. Thanks to you and all other contributing users!


7.66 and 7.704
System: Windows / AIX / Linux
Output: Mostly HTML, with some PDF, Excel and Lotus(!)
 
Posts: 147 | Location: Toronto (GTA) | Registered: May 25, 2005Report This Post
njsden
Virtuoso
posted Hide Post
I'm glad it helped! Please do keep in mind Dan's note of caution though, since as he points out manual adjustments to the master file get broken after a "Synonym refresh" so if the latter happens make sure to manually edit the master and adjust it accordingly.

- Neftali.


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) How to compare 2 days effectively (without pulling the whole table from DB)?

Copyright © 1996-2020 Information Builders