quote:

TABLE FILE MHTPAR
SUM
MAX.PARCGRADE
BY PARCODE
WHERE RECORDLIMIT IS 500
ON TABLE HOLD AS HPROP
END


JOIN CLEAR *
JOIN PARCODE IN HPROP TO ALL PARCODE IN MHTPEV AS JPEVAPPR
JOIN PARCODE IN HPROP TO PARCODE IN MHTPNA AS JPNAPPR
END

DEFINE FILE HPROP
TODAY/DMYY = &DMYY;
DATER/DMYY = DATEADD(TODAY,'D',-365);
APPR_COUNT_TEMP/I6 = IF PEVDATE_RAIS GE DATER
AND PETCODE IN ('CY' 'CS' 'CX' 'CH')
AND PEVAMOUNT LE 10000 THEN 1 ELSE 0;
MC_TEMP/I5 = IF PETCODE EQ 'CS' THEN 1 ELSE 0;
GROUP/I9 = PARGROUP_NUMBER;
END

TABLE FILE HPROP
SUM
APPR_COUNT_TEMP
BY GROUP
BY PARCODE
WHERE APPR_COUNT_TEMP EQ 1
ON TABLE HOLD AS HAPPR_COUNT1
END

TABLE FILE HPROP
SUM
MC_TEMP
BY PARCODE
BY HIGHEST 1 PEVCODE NOPRINT
BY GROUP
WHERE PETCODE IN ('CM' 'CN' 'CY' 'CS' 'CX' 'CH' 'CW')
ON TABLE HOLD AS HMAX_CRED1 FORMAT FOCUS INDEX GROUP
END

TABLE FILE HAPPR_COUNT1
SUM
APPR_COUNT_TEMP
BY GROUP
WHERE GROUP GT 0
ON TABLE HOLD AS HAPPR_COUNT2
END

JOIN CLEAR *
JOIN GROUP IN HAPPR_COUNT1 TO GROUP IN HAPPR_COUNT2 AS JPNAPPR2
END

DEFINE FILE HAPPR_COUNT1
APPR_COUNT/I8 =IF HAPPR_COUNT2.APPR_COUNT_TEMP EQ 0 THEN
HAPPR_COUNT1.APPR_COUNT_TEMP
ELSE HAPPR_COUNT2.APPR_COUNT_TEMP;
END

TABLE FILE HAPPR_COUNT1
PRINT
APPR_COUNT
BY PARCODE
ON TABLE HOLD AS HAPPR_COUNT FORMAT FOCUS INDEX PARCODE
END

TABLE FILE HMAX_CRED1
SUM
MC_TEMP
BY GROUP
WHERE GROUP GT 0
ON TABLE HOLD AS HMAX_CRED2 FORMAT FOCUS INDEX GROUP
END

JOIN CLEAR *
JOIN GROUP IN HMAX_CRED1 TO GROUP IN HMAX_CRED2 AS JMAXCRED

DEFINE FILE HMAX_CRED1
MC_TEMP2/I5 =IF HMAX_CRED2.MC_TEMP EQ 0 THEN HMAX_CRED1.MC_TEMP
ELSE HMAX_CRED2.MC_TEMP;
MAX_CRED/A1 = IF MC_TEMP2 EQ 1 THEN 'Y' ELSE 'N';
END

TABLE FILE HMAX_CRED1
PRINT
MAX_CRED
BY PARCODE
ON TABLE HOLD AS HMAX_CRED FORMAT FOCUS INDEX PARCODE
END

JOIN CLEAR *
JOIN PARCODE IN HPROP TO PARCODE IN MHTBPU AS JBUS
JOIN PARCODE IN HPROP TO PARCODE IN MHTPNA AS JPNAREST

DEFINE FILE HPROP
RESTRICT_M_COUNT/I5 = IF TPUCODE EQ 'R' THEN 1 ELSE 0;
GROUP/I9 = PARGROUP_NUMBER;
END

TABLE FILE HPROP
PRINT
RESTRICT_M_COUNT
BY GROUP
BY PARCODE
ON TABLE HOLD AS HRESTRICT1
END

TABLE FILE HRESTRICT1
SUM
RESTRICT_M_COUNT
BY GROUP
WHERE GROUP GT 0
ON TABLE HOLD AS HRESTRICT2
END

JOIN CLEAR *
JOIN GROUP IN HRESTRICT1 TO GROUP IN HRESTRICT2 AS JPNAREST2


DEFINE FILE HRESTRICT1
RESTRICT_M/A1 = IF HRESTRICT2.RESTRICT_M_COUNT GT 0
OR HRESTRICT1.RESTRICT_M_COUNT GT 0 THEN 'Y' ELSE 'N';
END

TABLE FILE HRESTRICT1
PRINT
RESTRICT_M
BY PARCODE
ON TABLE HOLD AS HRESTRICT FORMAT FOCUS INDEX PARCODE
END

quote:

JOIN CLEAR *
JOIN GROUP IN HRESTRICT1 TO GROUP IN HRESTRICT2 AS JPNAREST2