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,

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?

Typo in the post. I'll update the 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 ' ...

quote:

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

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

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,

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,

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');

Thank you Tom. Next time I'm in Richmond, I owe you a beer!

quote:

JOIN AMSTEL IN RIGHT_HAND TO ALL AMSTEL IN BAR AS DRINK_EM_UP

I'll drink to that!