I have a situation where I need to accept a paramter then reformat it and use in the where clause so that the result set from the database is small. Here is the example.
BY HH_CUST_NUM WHERE ( CB_MAJ_ACCT_NUM EQ '00004' ); WHERE ( BILL_YEAR EQ '&ENTER_YEAR.Enter the year.' );
BILL_YEAR is a defined field. I want to accept the year in ccyy format from the user and create a variable using ENTER_YEAR in the MM/DD/YY format. Instead of BILL_YEAR I want to use my database field so that I can force the FOCUS to use database fiedl in the where clause.
Any help is appreciated.
September 16, 2004, 01:22 PM
Steve C
To do what you want, you will need to manipulate the input source year to become a full date value. Here is the code:
DEFINE FILE EMPLOYEE ND/YY = &INPUT_YEAR; ND1/YYMD=ND; NEWDT/I8MDYY = DATECVT(ND1, 'YYMD', 'I8MDYY'); END TABLE FILE EMPLOYEE BY NEWDT PRINT EMP_ID LAST_NAME END
The above converts the year to a full YYMD format, which is set to Jan 1 of the year entered. Then use DATECVT to covert it back to a legacy date (Integer) field in the format of your choice.This message has been edited. Last edited by: <Mabel>,
September 17, 2004, 05:54 AM
Piipster
Try to avoid manipulating input values as DEFINEd fields. Defined fields are evaluated once for every row of extracted data. Do the manipulation with Dialog Manager Variables.