I need to be able to obtain the current/system date as an external variable using the following format.
Format MMM-YY (A150V) example: FEB-06

At present, I have virtual fields created via define to capture this however the table has millions of rows and I need to be able to make it an external variable; here's the define statement:
DEFINE FILE filename ADD
SYSDTTM/HYYMDS=HGETC(8, 'HYYMDS');
SYSMONTH/A25=HNAME(SYSDTTM, 'MONTH', 'A25');
SYSYEAR/A4=HNAME(SYSDTTM, 'YEAR', 'A4');
SYSYR/A2=EDIT(SYSYEAR, '$$99');
SYSMON/A3=EDIT(SYSMONTH, '999$$$$$$');
CSYSDATE/A150=SYSMON || '-' || SYSYR;
END

Any help is greatly appreciated!

Sara, we don't know what system you're on (you could edit your profile and tell us),
but you can do manipulations in dialog manager on the system date, either &DATE or &YYMD.
Try them both and see which one you like as a starting point.

Sorry about that, I'm on WF Reporting 7.

Either date works for me...I'm able to fetch system date just having trouble getting it into the format I need.

Thank you for the response however the month needs to be upper case and the format A150V. I tried the UPCASE function on this and received no data.

code:
-SET &SHOWDT2=UPCASE(150, &DATEMt, 'A150V');
-SET &SHOWDT=&SHOWDT2 || '-' || &DATEY;
TABLE FILE file
PRINT
CC_KEY
TIME_KEY
&SHOWDT2
ON TABLE NOTOTAL
END

Error:
0 ERROR AT OR NEAR LINE 7 IN PROCEDURE ADHOCRQ FOCEXEC *
(FOC003) THE FIELDNAME IS NOT RECOGNIZED: FEB
BYPASSING TO END OF COMMAND
(FOC009) INCOMPLETE REQUEST STATEMENT

However when using similar code:
-SET &SHOWDT2=UPCASE(150, &DATEMt, 'A150V');
-SET &SHOWDT=&SHOWDT2 || '-' || &DATEY;
-RUN
- TYPE START_DATE=&SHOWDT end_date=&SHOWDT2
-RUN
-EXIT

All works as planned:
START_DATE=FEB-06

In the TABLE request you are basically asking it to print out a field called &SHOWDT2 which is equal to FEB. Do you have a field called FEB?

Be sure to use an ECHO when you are testing with amper variables so that you can see how your code is being resolved. It'll make the errors easier to understand.

The error you are getting is (as Karen states) because you do not have a field named FEB in your MFD, so to display the value of a variable in a TABLE request you will need to place it into a COMPUTED field -

-SET &SHOWDT2=UPCASE(150, &DATEMt, 'A150V');
-SET &SHOWDT=&SHOWDT2 || '-' || &DATEY;
TABLE FILE file
PRINT
CC_KEY
TIME_KEY
COMPUTE DISP_DATE/A6 = '&SHOWDT2.EVAL';
ON TABLE NOTOTAL
END

T

I hate to nitpick but instead of using the UPCASE function, just use &DATEMT (notice the capital T).

Both of the following statements:

-SET &SHOWDT2=UPCASE(150, &DATEMt, 'A150V');
-SET &SHOWDT=&SHOWDT2 || '-' || &DATEY;

Can be reduced to this one statement:

-SET &SHOWDT = &DATEMT | '-' | &DATEY;

Then the rest of the code is the same.

TABLE FILE file
PRINT
CC_KEY
TIME_KEY
COMPUTE DISP_DATE/A6 = '&SHOWDT2.EVAL';
ON TABLE NOTOTAL
END


I'm sorry but it was bugging me that using &DATEMT was not suggested. No criticism intended to other solutions posted. I just wanted to mention the &DATEMT because we all are always look for the least amount of code to accomplish a task. The '.EVAL' is not needed either in the code above.

Thank you Mickey...any shortcuts are greatly appreciated!

I think I've managed to confuse what the need is. I had pulled the variable in the report to make sure its calculating correctly, which I should have done with the ECHO as noted by Karen/Piipster..(thanks!), however what I really need is to only select those records where the variable matches the time_key.

Ok, so going with a portion of the code suggested by Mickey I modified it to use the variable in the where statement and receive the following error message.


0 ERROR AT OR NEAR LINE 6 IN PROCEDURE ADHOCRQ FOCEXEC *
(FOC258) FIELDNAME OR COMPUTATIONAL ELEMENT NOT RECOGNIZED: FEB
BYPASSING TO END OF COMMAND
(FOC009) INCOMPLETE REQUEST STATEMENT

Code:
-SET &SHOWDT = &DATEMT | '-' | &DATEY;

TABLE FILE file
PRINT
CC_KEY
TIME_KEY
WHERE &SHOWDT EQ TIME_KEY;
ON TABLE NOTOTAL
END

What am I missing here? Am I using funtionality that is not suitable to the need?

Forgive me for my ignorance and thank you for your patience. I've only been using this tool for a few months and have very little development background...if you couldn't already tell .

Guess I shouldn't have spoken so quickly and tried a few more things first. I've identified the answer...please find below for reference.

I'd like to say thank you to everyone for their help, patience, and willingness. I very much appreciate it!

-SET &SHOWDT = &DATEMT | '-' | &DATEY;
TABLE FILE file
PRINT
CC_KEY
TIME_KEY
WHERE TIME_KEY EQ '&SHOWDT';
ON TABLE NOTOTAL
END

Let me see if I can explain this with as few words as possible.

To fix your code, flip-flop the &SHOWDT and the TIME_KEY in your WHERE statement. Then, put single quote marks around &SHOWDT.

This is what is happening. Whenever FOCUS sees an &variable it takes the value for the variable and sticks it in where the variable name is. In your case FEB-06 is put in the statement so it looks like this:

WHERE FEB-06 EQ TIME_KEY;

Now the WHERE statement is very flexible but if something does not have quotes around it, it will think it is a fieldname. Since FEB-06 does not have quotes around it, it looks for a field with this name instead of treating it as a value.

Dialogue MAnager variables (the ones with & or &&) are used to dynamically put a character string into a piece of code. After all the variables have been resolved, it then evaluates the statement.

This is a generalized explanation of a more complicated and very powerful feature of the FOCUS language.

I hope this helps.

Fabulous! Thank you so much for your help!