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     [CASE-CLOSED]Testing dates from ORACLE

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[CASE-CLOSED]Testing dates from ORACLE
 Login/Join
 
tcox
Member
posted
Hi All.

I have a date coming from an ORACLE database (date time field) and I need to check that this falls within the past 7 days.
I know about defining a variable = &DMY to get todays date, and presumably I could then have another variable = that one -7 for the start of the date range, but I cannot seem to get the formats of this date and my ORACLE date to match so that I can get them to work in the filter.
I do not have access to modify any code so I'm limited to what can be done via the infoassist interface.
Could anyone help please?

Thank you.

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


Release 8
Windows 7
Excel/HTML/PDF
Oracle 11g
 
Posts: 16 | Registered: October 23, 2012Report This Post
Tom Flynn
Expert
posted Hide Post
Lookup the HDATE function


Tom Flynn
WebFOCUS 8.1.05 - PROD/QA
DB2 - AS400 - Mainframe
 
Posts: 1972 | Location: Centennial, CO | Registered: January 31, 2006Report This Post
tcox
Member
posted Hide Post
Thanks Tom, the HDATE function was good for converting my Oracle date but I still have a problem.
I defined a field from the Oracle date as

HDATE(db_view.SESSION_CREATION_DATE , 'YYMD')

(called sdate)


Then I defined a field for todays date as

&YYMD (called this field td)

and finally one more as todays date - 7 days using

DATEADD (td , 'D', -7) (called Dminus7)

They all have the format YYMD, but when I try to use these in a filter such as

sdate greater than dminus7

I get the error 'the format of the test value is inconsistent with field format: dminus7'

Sorry if this is something really simple but Infoassist is not something I use a great deal (yet!)


Release 8
Windows 7
Excel/HTML/PDF
Oracle 11g
 
Posts: 16 | Registered: October 23, 2012Report This Post
Tom Flynn
Expert
posted Hide Post
Hi tcox,
Dates have to be "smart dates".
Try this DEFINE code:
  
SDATE/YYMD = HDATE(db_view.SESSION_CREATION_DATE , 'YYMD');
CUR_DT/YYMD = DATECVT(&YYMD, 'I8YYMD', 'YYMD');
CUR_MINUS_7/YYMD = DATEADD(CUR_DT, 'D', -7);

Then, in the code:
WHERE SDATE GE CUR_MINUS_7 AND SDATE LE CUR_DT;


Tom Flynn
WebFOCUS 8.1.05 - PROD/QA
DB2 - AS400 - Mainframe
 
Posts: 1972 | Location: Centennial, CO | Registered: January 31, 2006Report This Post
Tom Flynn
Expert
posted Hide Post
tcox,
This is an excellent resource book addressing WebFOCUS dates, well worth the price; may be available at Summit as well...

1001 Ways to Work with Dates in WebFOCUS


Tom Flynn
WebFOCUS 8.1.05 - PROD/QA
DB2 - AS400 - Mainframe
 
Posts: 1972 | Location: Centennial, CO | Registered: January 31, 2006Report This Post
tcox
Member
posted Hide Post
Hi Tom.
Thanks for all your help on this.
The good news is that I did exactly what you suggested and managed to get into the 'managed reporting applet' so that I could change the code, and ran the report from there and it worked perfectly.
I then went back to the Infoassist user interface (which is where we want our users to develop reports) and it still comes up with the same error about inconsistent formats and it will not run.

Just as another idea, I read about Report Assistant which I also managed to get into via the MR-Applet.
I re-created my report in there and ran it.
It worked perfectly again.

So I now have three ways to run this: two of which work fine, but the third doesn't and that's the one I need for the users.

So why is the user interface / interactive version failing?


Release 8
Windows 7
Excel/HTML/PDF
Oracle 11g
 
Posts: 16 | Registered: October 23, 2012Report This Post
Tom Flynn
Expert
posted Hide Post
tcox,
I opened IA and was able to generate the 3 new columns successfully(see image below). You probably need to remove db_view. from SDATE:
SDATE/YYMD = HDATE(SESSION_CREATION_DATE, 'YYMD');

Also, make sure when you enter the DEFINE that the Format is changed to YYMD and there is no semi-colon at the end...
If it still doesn't work, contact IBI...
Good Luck!
Tom



Tom Flynn
WebFOCUS 8.1.05 - PROD/QA
DB2 - AS400 - Mainframe
 
Posts: 1972 | Location: Centennial, CO | Registered: January 31, 2006Report This Post
tcox
Member
posted Hide Post
I have closed this case to refer it to IBI.
Currently the report works fine if started from the managed reporting applet.
Thank you for you support and advice.

Tony...


Release 8
Windows 7
Excel/HTML/PDF
Oracle 11g
 
Posts: 16 | Registered: October 23, 2012Report This Post
jgelona
Master
posted Hide Post
I think you guys are making this too hard. What follows is based on the assumption that if today is Monday, you want data where SESSION_CREATION_DATE is from last Monday through Sunday. I also assume that in the master, SESSION_CREATION_DATE is an H format (date time).

-SET &7DAYS=AYMD(&YYMD,-7,'I8YYMD');
 TABLE FILE XXXX
 PRINT FLDA FLDB ...
    BY ...
 WHERE SQL.TRUNC(SESSION_CREATION_DATE) GE '&7DAYS'
   AND SQL.TRUNC(SESSION_CREATION_DATE) LT '&YYMD'
 END


Another option if you don't have the ability to use the "SQL." prefix for Oracle functions is to do this in the master.

FIELD=SDTTM,SESSION_CREATION_DATE,HYYMDS,HYYMDS,$
FIELD=SDATE,SESSION_CREATION_DATE,YYMD,DATE,$


Then replace SQL.TRUNC(SESSION_CREATION_DATE) with SDATE.

I do this all the time.

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


In FOCUS since 1985. Prod WF 8.0.08 (z90/Suse Linux) DB (Oracle 11g), Self Serv, Report Caster, WebServer Intel/Linux.
 
Posts: 975 | Location: Oklahoma City | Registered: October 27, 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     [CASE-CLOSED]Testing dates from ORACLE

Copyright © 1996-2020 Information Builders