Hi,

I am trying to implement the following. But in ouput excel it is giving ******** in the required field.

DEFINE FILE AAAA
DATEA/A10 = TODAY(DATEA);
DATEH/HDMYYS = HINPUT(10, DATEA, 8, 'HDMYYS');
D_TOD/YYMD=HDATE(DATEH,'YYMD');
DATE_SER/YYMD MISSING ON = HDATE(BASE_SERVICE_DT,'YYMD');
-* SERV_YRS1/I8 = DATEDIF(DATE_SER,D_TOD,'Y');
SERV_YRS/A10 = IF DATE_SER IS MISSING THEN ' ' ELSE FTOA(DATEDIF(DATE_SER,D_TOD,'Y'), '(A10)', SERV_YRS);
-*
EMPL_STATUS/A34 = IF L9SF_EMPL_STATUS = ' ' THEN ' '
ELSE EDIT(L9SF_EMPL_STATUS,'9') |' - '| L9SF_EMP_STAT_DESC;
END
-RUN

TABLE FILE AAAA
PRINT SERV_YRS
EMPL_STATUS
ON TABLE PCHOLD AS WFGN FORMAT EXL2K
END
-RUN
-*
-*
-EXIT

It is not giving any error but in ouput excel all required row don't have data instead it is showing as ********.

Please suggest were I am going wrong?

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

Comment out your ON TABLE PCHOLD statement and allow WebFOCUS to generate the output in HTML format. Also put an ECHO statment at the top of the program. These steps should allow you to see any errors generated and determine if reasonable output is being produced before moving to Excel output.

-SET &ECHO=ALL;
.
.
.
-*ON TABLE PCHOLD AS WFGN FORMAT EXL2K
.
.

AS a starting point
FTOA is used for Decimal values not Alpha
DATEDIF returns Integer values not Alpha

Also do not use '=' instead of 'EQ' in defines and computes, it's not correct and although it might work today chances are it will not in the future

I tried Dan's steps, but was not successful, it gave starred output in html too without any error.

As JG suggested i made my define more simple as below

DEFINE FILE AAAA
DATEA/A10 = TODAY(DATEA);
DATEH/HDMYYS = HINPUT(10, DATEA, 8, 'HDMYYS');
D_TOD/YYMD=HDATE(DATEH,'YYMD');
DATE_SER/YYMD MISSING ON = HDATE(BASE_SERVICE_DT,'YYMD');
SERV_YRS/I8 = IF DATE_SER IS MISSING THEN ' ' ELSE DATEDIF(DATE_SER,D_TOD,'Y');
-*
EMPL_STATUS/A34 = IF L9SF_EMPL_STATUS = ' ' THEN ' '
ELSE EDIT(L9SF_EMPL_STATUS,'9') |' - '| L9SF_EMP_STAT_DESC;
END
-RUN

TABLE FILE AAAA
PRINT SERV_YRS
EMPL_STATUS
ON TABLE PCHOLD AS WFGN FORMAT EXL2K
END
-RUN
-*
-*
-EXIT

But it gave format compatability error stating SERV_YRS is not compatible, but then I tried as below
DEFINE FILE AAAA
DATEA/A10 = TODAY(DATEA);
DATEH/HDMYYS = HINPUT(10, DATEA, 8, 'HDMYYS');
D_TOD/YYMD=HDATE(DATEH,'YYMD');
DATE_SER/YYMD MISSING ON = HDATE(BASE_SERVICE_DT,'YYMD');
SERV_YRS/I8 =DATEDIF(DATE_SER,D_TOD,'Y');
-*
EMPL_STATUS/A34 = IF L9SF_EMPL_STATUS = ' ' THEN ' '
ELSE EDIT(L9SF_EMPL_STATUS,'9') |' - '| L9SF_EMP_STAT_DESC;
END
-RUN

TABLE FILE AAAA
PRINT SERV_YRS
EMPL_STATUS
ON TABLE PCHOLD AS WFGN FORMAT EXL2K
END
-RUN
-*
-*
-EXIT
it ran properly and gave me output, but not as per the requirement, even for rows which don't have BASE_SERVICE_DT had some value as 109.

Any suggestion of what could be wrong with the IF loop in DEFINE

quote:

109

A magic number, or rather the result of 2010 - 1901

MISSING is not valid in DATEDIF and I suspect treated as the WebFOCUS base year.

try

SERV_YRS/I8 = IF DATE_SER IS MISSING THEN 0 ELSE DATEDIF(DATE_SER,D_TOD,'Y');

quote:

SERV_YRS/I8 = IF DATE_SER IS MISSING THEN 0 ELSE DATEDIF(DATE_SER,D_TOD,'Y');

Thank you. It worked. It was simple logic, which I missed.

FYI

If you get ******** in relation to a number, usually means that the number is too big for the field format size.

MKS,

Fix the syntax errors as JG identified to avoid unexpected results.

In my experience(hope you gurus will validate this for me), that it is almost always not a good time to test for missing values in DEFINE. I have better luck with COMPUTE or put it in the hold file first.

Hua