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     Beginning of Month/Quarter/Year

Read-Only Read-Only Topic
Go
Search
Notify
Tools
Beginning of Month/Quarter/Year
 Login/Join
 
ColdWhiteMilk
Platinum Member
posted
I'm using WebFOCUS 7.1.3.

I have four global variables that I am using to create the date range for my query criteria:

-SET &TODAY = &YYMD;
-SET &FSTOFWK = DATEMOV (&TODAY, 'BOW');
-SET &CURWED = &FSTOFWK+2;
-SET &LSTWED = &CURWED-7;

TABLE FILE JOB
PRINT
JOB.EMPLID

WHERE JOB.EFFDT GE &LSTWED AND JOB.EFFDT LE &CURWED

ON TABLE PCHOLD FORMAT EXL2K
END


This works great, and creating the values as global variables rather than defines or prompts has made my queries dramatically faster.

Now I want to change the global variables so that I am using beginning of the month and end of the month. I've got:

-SET &CURBOM = DATEMOV (&TODAY, 'BOM');
-SET &CUREOM = DATEMOV (&TODAY, 'EOM');

and then I update the where clause to:

WHERE JOB.EFFDT GE &CURBOM AND JOB.EFFDT LE &CUREOM

Once I do this, the query returns an error message that says that the line with the "Where" contains an unrecognized arguement.

Has anyone ever seen this before, and was able to fix it?

Any help would be appreciated.

Thanks!

This message has been edited. Last edited by: ColdWhiteMilk,
 
Posts: 241 | Location: Bethesda, MD | Registered: August 14, 2007Report This Post
<JG>
posted
quote:
-SET &CURBOM = DATEMOV (&TODAY, 'BOM');
-SET &CURBOM = DATEMOV (&TODAY, 'EOM');

and then I update the where clause to:

WHERE JOB.EFFDT GE &CURBOM AND JOB.EFFDT LE &CUREOM



Your'e not setting &CUREOM, you have CURBOM twice

Typo in the post or the cause?
 
Report This Post
ColdWhiteMilk
Platinum Member
posted Hide Post
Typo in the post. I'll update the post


Production - 7.6.4
Sandbox - 7.6.4
 
Posts: 241 | Location: Bethesda, MD | Registered: August 14, 2007Report This Post
FrankDutch
Virtuoso
posted Hide Post
your &CUREOM is a date string, so you have to say

WHERE JOB.EFFDT GE '&CURBOM' AND JOB.EFFDT LE '&CUREOM'


don't forget the ' ...




Frank
prod: WF 7.6.10 platform Windows,
databases: msSQL2000, msSQL2005, RMS, Oracle, Sybase,IE7
test: WF 7.6.10 on the same platform and databases,IE7

 
Posts: 2387 | Location: Amsterdam, the Netherlands | Registered: December 03, 2006Report This Post
ColdWhiteMilk
Platinum Member
posted Hide Post
quote:
WHERE JOB.EFFDT GE &LSTWED AND JOB.EFFDT LE &CURWED


Why would this work then, but the &CURBOM/&CUREOM would not work?


Production - 7.6.4
Sandbox - 7.6.4
 
Posts: 241 | Location: Bethesda, MD | Registered: August 14, 2007Report This Post
N.Selph
Guru
posted Hide Post
You can't use DATEMOV on I8YYMD dates. You can only use it on smart dates.

You need to convert your system date to a smart date first, use DATEMOV and then convert it back. I do this sort of thing all the time and it works.

-SET &DATE1=DATECVT(&YYMD,'I8YYMD','YYMD');
-SET &CURBOM=DATEMOV(&DATE1,'BOM');
-SET &DATE2=DATECVT(&CURBOM,'YYMD','I8YYMD');

This message has been edited. Last edited by: N.Selph,


(Prod: WebFOCUS 7.7.03: Win 2008 & AIX hub/Servlet Mode; sub: AS/400 JDE; mostly Self Serve; DBs: Oracle, JDE, SQLServer; various output formats)
 
Posts: 391 | Location: California | Registered: April 14, 2003Report This Post
ColdWhiteMilk
Platinum Member
posted Hide Post
Thank you again.

This appears to be working now.

I can't believe how much faster my queries are running when I use global variables rather than prompts in the criteria!

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


Production - 7.6.4
Sandbox - 7.6.4
 
Posts: 241 | Location: Bethesda, MD | Registered: August 14, 2007Report This Post
ColdWhiteMilk
Platinum Member
posted Hide Post
So then would these work for "Beginning of Last Month" and "End of Last Month"?:

-SET &LSTBOM = DATECVT(DATEMOV(DATECVT((DATEADD(&TODAY, 'M', -1)), 'I8YYMD', 'YYMD'), 'BOM'),'YYMD', 'I8YYMD');
-SET &LSTEOM = DATECVT(DATEMOV(DATECVT((DATEADD(&TODAY, 'M', -1)), 'I8YYMD', 'YYMD'), 'EOM'),'YYMD', 'I8YYMD');


Production - 7.6.4
Sandbox - 7.6.4
 
Posts: 241 | Location: Bethesda, MD | Registered: August 14, 2007Report This Post
ColdWhiteMilk
Platinum Member
posted Hide Post
Thank you Tom. Next time I'm in Richmond, I owe you a beer!


Production - 7.6.4
Sandbox - 7.6.4
 
Posts: 241 | Location: Bethesda, MD | Registered: August 14, 2007Report This Post
Prarie
Virtuoso
posted Hide Post
quote:
JOIN AMSTEL IN RIGHT_HAND TO ALL AMSTEL IN BAR AS DRINK_EM_UP


I'll drink to that! Wink


In Focus since 1993. WebFOCUS 7.7.03 Win 2003
 
Posts: 1903 | Location: San Antonio | Registered: February 28, 2005Report This Post
linus
Platinum Member
posted Hide Post
Not to sound mean, but I want to correct your verbaage. You are creating "local" variables, not "global". Global variables start with 2 ampersands, local variables start with 1. Local variables only exist for the current procedure, where global variables remain in effect for all procedures executed during an entire WF session.


WF 7.7.05
HP-UX - Reporting Server, Windows 2008 - Client, MSSQL 2008, FOCUS Databases, Flat Files
HTML, Excel, PDF
 
Posts: 149 | Location: Dallas, TX | Registered: June 08, 2007Report 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     Beginning of Month/Quarter/Year

Copyright © 1996-2020 Information Builders