Hello, If I could get some assistance I would appreciate it.

I am calculating the difference in "Days" between two(2) dates.

I am attempting to convert those values to alpha for printing. In my Print statement I printed all four(4) fields...(ins_diffu, ins_diffv, xdiffu, xdiffv)

ins_diffu and ins_diffv - all contain expected numeric values

xdiffu and xdiffv - only produce zeros(0).

I'm missing something, but am drawing a blank. any assistance would be appreciated



These values are out of my SQL:
INS_DIFFU=DATEDIFF(D,T1.CONTRACTDATE,T2.GOVT_UFMIP_DATE_REMITTED),
INS_DIFFV=DATEDIFF(D,T1.CONTRACTDATE,T2.GOVT_VAFF_DATE_REMITTED),

Converted values for printing:

-***************************************
-*This is for the Number Days from Closing field*
-***************************************
XDIFFU/A4 =FTOA(INS_DIFFU,'(I4)',XDIFFU);
XDIFFV/A4 =FTOA(INS_DIFFV,'(I4)',XDIFFV);
DSPDFC/A4 = IF LNKEY NE LAST LNKEY AND DISPLNTYP EQ 'FHA' THEN XDIFFU
ELSE IF LNKEY NE LAST LNKEY AND DISPLNTYP EQ 'VA' THEN XDIFFV ELSE ' ';

quote:

I am attempting to convert those values to alpha for printing.

I don't understand why you have to convert these values.

It looks to me...that if I don't convert those values, I get an if-then-else error because of the check on the Alpha values 'FHA' and 'VA'..I get the message that comparison between alpha and numeric is not permitted..

Hi Tlbrydie

Try this..


INS_DIFFU/I4=DATEDIFF(D,T1.CONTRACTDATE,T2.GOVT_UFMIP_DATE_REMITTED);
INS_DIFFV/I4=DATEDIFF(D,T1.CONTRACTDATE,T2.GOVT_VAFF_DATE_REMITTED);


I added the format of I4 and changed the comma at the end to a semicolon.

I agree with Babak you shouldn't have to change to alpha..

Chuck, the comma was because these are two statements out of my sql pull...there are additional columns...these are just to fields that I am calculating out of the sql.

quote:

It looks to me...that if I don't convert those values, I get an if-then-else error because of the check on the Alpha values 'FHA' and 'VA'...I get the message that comparison between alpha and numeric is not permitted

quote:

DISPLNTYP EQ 'FHA'

Does DISPLNTYP is defined as an Alpha ? This is the only comparison that you seems to have.

It is difficult to help since we don't have the whole picture of your data definition (.mas) and used code (.fex).

Attached below is most of the fex....


SQL
Select distinct
-*TLB:INS_DIFF=DATEDIFF(D,T1.CONTRACTDATE,T5.GOVT_MIC_LGC_RECEIVED),
INS_DIFFU=DATEDIFF(D,T1.CONTRACTDATE,T2.GOVT_UFMIP_DATE_REMITTED),
INS_DIFFV=DATEDIFF(D,T1.CONTRACTDATE,T2.GOVT_VAFF_DATE_REMITTED),
T1.LNKEY,
T1.LOANTYPE,
T1.CONTRACTDATE,
T2.GOVT_UFMIP_DATE_REMITTED,
T2.GOVT_VAFF_DATE_REMITTED
FROM EMPOWER_ODS.EMPOWER_WK.LN_MTGTERMS T1
LEFT OUTER JOIN EMPOWER_ODS.EMPOWER_WK.U_LN_POSTCLOSING_KEYDATES T2
ON T1.LNKEY = T2.LNKEY
LEFT OUTER JOIN EMPOWER_ODS.EMPOWER_WK.LN_EVENTS T3
ON T1.LNKEY = T3.LNKEY
LEFT OUTER JOIN EMPOWER_ODS.EMPOWER_WK.U_LN_GL_FUNDING T4
ON T1.LNKEY = T4.LNKEY
WHERE T1.LOANTYPE IN (22, 23, 26, 27)
AND T4.LOAN_CLOSED = 'Y'
AND T1.U_CP_MOD is NULL
AND T3.EVENTNUM = 314
AND T1.CONTRACTDATE >= '&STARTDT' AND T1.CONTRACTDATE<='&ENDDT'
ORDER BY INS_DIFFU, INS_DIFFV;


-****Putting data retrieved into Hold File****
TABLE FILE SQLOUT
PRINT *
ON TABLE HOLD AS TLBOUT1
END
-*-RUN
-*-EXIT
-*
-*
-*--------------------------------------------------------*
-*Creating Defines formatted for the Report *
-*--------------------------------------------------------*
-*
DEFINE FILE TLBOUT1
-*DAYS_OLD/I8=CURR_DT - SETTLEMENT_DAY ;
-*INS_DIFFU1/I8=DATEDIFF(CONTRACTDATE - GOVT_UFMIP_DATE_REMITTED);
-*INS_DIFFV2/I8=DATEDIFF(CONTRACTDATE - GOVT_VAFF_DATE_REMITTED);

INS_DIFFU1/I8=(CONTRACTDATE - GOVT_UFMIP_DATE_REMITTED);
INS_DIFFV2/I8=(CONTRACTDATE - GOVT_VAFF_DATE_REMITTED);
-*
DISP_DISB/MDYY MISSING ON=HDATE (CONTRACTDATE, 'MDYY' );
DISP_FHA/MDYY MISSING ON=HDATE (GOVT_UFMIP_DATE_REMITTED, 'MDYY' );
DISP_VA/MDYY MISSING ON=HDATE (GOVT_VAFF_DATE_REMITTED, 'MDYY' );

DISPLNTYP/A3= DECODE LOANTYPE (22 'FHA',
23 'FHA'
26 'VA'
27 'VA') ;

-*-********************************
-*-*This is for the Closing Date *
-*-********************************
-*DSPCLD/A10 = IF (LNKEY NE LAST LNKEY AND DISPLNTYP EQ 'FHA') THEN 'INS_DIFFU'
-* ELSE IF (LNKEY NE LAST LNKEY AND DISPLNTYP EQ 'VA') THEN 'INS_DIFFV' ELSE ' ';
-*-*
-*****************************
-*This is for the Date Remitted field*
-*****************************
DSPDR/MDYY = IF LNKEY NE LAST LNKEY AND DISPLNTYP EQ 'FHA' THEN DISP_FHA
ELSE IF LNKEY NE LAST LNKEY AND DISPLNTYP EQ 'VA' THEN DISP_VA ELSE ' ';
-*
-***************************************
-*This is for the Number Days from Closing field*
-***************************************
-*XDIFFU/A4 =FTOA(INS_DIFFU,'(I4)',XDIFFU);
-*XDIFFV/A4 =FTOA(INS_DIFFV,'(I4)',XDIFFV);
-*DSPDFC/I10 = IF LNKEY NE LAST LNKEY AND DISPLNTYP EQ 'FHA' THEN XDIFFU

DSPDFC/I8 = IF LNKEY NE LAST LNKEY AND DISPLNTYP EQ 'FHA' THEN INS_DIFFU1
ELSE IF LNKEY NE LAST LNKEY AND DISPLNTYP EQ 'VA' THEN INS_DIFFV2 ELSE ' ';

-*DSPDFC/I10 = IF LNKEY NE LAST LNKEY AND DISPLNTYP EQ 'FHA' THEN INS_DIFFU
-* ELSE IF LNKEY NE LAST LNKEY AND DISPLNTYP EQ 'VA' THEN INS_DIFFV ELSE ' ';


-**************************
-*This is for the PASS/FAIL field*
-**************************
DSPASSFAIL/A4 = IF LNKEY NE LAST LNKEY AND DISPLNTYP EQ 'FHA' AND XDIFFU LE '10' THEN 'Pass'
ELSE IF LNKEY NE LAST LNKEY AND DISPLNTYP EQ 'VA' AND XDIFFV GT '10' THEN 'Fail' ELSE ' ';
-*
-*DSPASSFAIL/I4 = IF LNKEY NE LAST LNKEY AND DISPLNTYP EQ 'FHA' AND INS_DIFFU LE 10 THEN 'Pass'
-* ELSE IF LNKEY NE LAST LNKEY AND DISPLNTYP EQ 'VA' AND INS_DIFFV GT 10 THEN 'Fail' ELSE ' ';
-*
PASSNUM/I4 = IF DSPASSFAIL EQ 'Pass' THEN 1 ELSE 0;
TTLPASS/I4=TTLPASS+PASSNUM;
FAILNUM/I4 = IF DSPASSFAIL EQ 'Fail' THEN 1 ELSE 0;
TTLFAIL/I4=TTLFAIL+FAILNUM;
COUNTER/I4 = IF LNKEY NE LAST LNKEY THEN COUNTER+1 ELSE COUNTER;
PERCENTPASS/D6%=(TTLPASS/COUNTER) * 100;
PERCENTFAIL/D6%= (100) - (PERCENTPASS) ;
SORTFILLER/I5=1;
END
-*-RUN
-*-EXIT



TABLE FILE TLBOUT1
PRINT
INS_DIFFU
INS_DIFFV
INS_DIFFU1
INS_DIFFV2
DISP_DISB
DISP_FHA
DISP_VA
DISPLNTYP
DSPDR
DSPDFC
DSPASSFAIL
PASSNUM
TTLPASS
FAILNUM
TTLFAIL
COUNTER
PERCENTPASS
PERCENTFAIL
SORTFILLER
LNKEY
LOANTYPE
CONTRACTDATE
GOVT_UFMIP_DATE_REMITTED
GOVT_VAFF_DATE_REMITTED
ON TABLE HOLD AS REMITTED1
END
-*-RUN
-*-EXIT
-*
-*
-DEFAULTH &WF_SUMMARY='Summary';
-DEFAULTH &WF_TITLE='WebFOCUS Report'
-*
TABLE FILE REMITTED1
PRINT
SUM DSPASSFAIL NOPRINT
-*PERCENTPASS1 NOPRINT
LNKEY AS 'Loan Number'
DISPLNTYP AS 'Loan Type'
DISP_DISB AS 'Disbursed Date'
DSPDR AS 'Date Remitted'
DSPDFC AS 'Days from Closing – Remitted <=10'
DSPASSFAIL AS 'Pass/Fail'
INS_DIFFU AS 'DIFFU'
INS_DIFFV AS 'DIFFV'
INS_DIFFU1 AS 'DIFFU1'
INS_DIFFV2 AS 'DIFFV2'
BY SORTFILLER NOPRINT
-*
ON SORTFILLER SUBFOOT

-*SUBFOOT
"<+0>Number of Loans :"<+0>Total Loans Passed :"<+0>% of Loans Passed :"<+0>Total Loans Fail :"<+0>% of Loans Fail :