Hi I tried to add to my previous post of Accepting Parameteres but it would not let me do that. So I had to start a new one.

Steve,

Thanks for the response. How do I use these values in my where clause the following code refuses to work.

DEFINE FILE CUSTBT
BILL_MONTH/M=HI_BILLING_8_DATE;
BILL_YEAR/YY=HI_BILLING_8_DATE;
ND/YY=&INPUT_YEAR;
ND1/YYMD=ND;
YEAR_BEGIN/I8YYMD = DATECVT(ND1, 'YYMD', 'I8YYMD');
YEAR_END/YYMD = DATEMOV(ND1,'EOY');
END
TABLE FILE CUSTBT
SUM
CA_CUST_NAME
HI_EXT_PRICE
BY
CB_CUST_NUM
WHERE ( CB_MAJ_ACCT_NUM EQ '00004' ) AND ( HI_BILLING_8_DATE GE YEAR_BEGIN ) AND ( HI_BILLING_8_DATE LE YEAR_END );

I want the YEAR_BEGIN and YEAR_END values to be substituted. I doubt that I am not doing it right because when I see the SQL trace, I donot see the where clause passed to DB2 engine.

Thanks.

It's my understanding when using a define based selection against DB2, the where is performed after the DB2 fetch. Do you have any real fields you can select on also?

If you want to substitute values for any object
in a report you need to use Amper variable, ie
&STARTDATE, and &ENDATE ..
Your WHERE should be something like..
WHERE ( CB_MAJ_ACCT_NUM EQ '00004' ) AND ( HI_BILLING_8_DATE GE &YEAR_BEGIN.Enter Beginning date. ) AND ( HI_BILLING_8_DATE LE &YEAR_END.Enter Ending date. );

Try running this in Developer Studio and a default Form should be generated asking you
for these two values.

You can then make up your own form, in Layout Tool, or whatever.

Well, there are a couple of issues. First, the date values in your database, are they Integer format, that is the date expressed as a number (ie. 20040916) or are they date format, which is expressed as the number of seconds since some base date? (You should be able to see this from the Actual format of the field in your Master file)

Your variable END_YEAR is in Date format (in Focus terms known as in new date format) and your YEAR_BEGIN is in Integer format (aka Old or Legacy date format in Focus). This will never work! You need to use the same format for both dates, as they are being compared to the same field.

Once you fix that, you need to use &INPUT_DATE with a prompt to get the user to enter the date. Something like

ND/YY='&INPUT_YEAR.Enter Year';
This will not work in Developer Studio, but is OK under MRE. How will you deploy this focexec in the end? That should dictate how you collect the data from the user. You may have to build a simple input screen in HTML to launch your procedure if it is not running through MRE.

Dialog Manager Variables are the way to go.

-PROMPT &ENTER_YEAR.Enter the year.I4.
-SET &BEG_DATE = '01/01/' |
- EDIT(ENTER_YEAR,'$$99');
-SET &END_DATE = '12/31/' |
- EDIT(ENTER_YEAR,'$$99');


(if you want the 4 char year just use &ENTER_YEAR)

and in the WHERE statement

WHERE db_date GE 'BEG_DATE';
WHERE db_date LE '&END_DATE';