online webfocus max function?
online webfocus max function?
(OP)
I have no problem do it on batch, use SQL to create a hold file. Anyway how to do it online?
SQL DB2
SELECT A.POL_ID, A.CO_ID, A.CVG_NUM,
B.CLI_ID,
C.CLI_FIRST_NM,
C.CLI_LAST_NM
FROM TABLE1 A, TABLE2 B, TABLE3 C
WHERE A.CO_ID = B.CO_ID AND
A.POL_ID = B.POL_ID AND
A.CVG_NUM = B.CVG_NUM AND
B.CLI_ID = C.CLI_ID AND
C.EFF_DATE = (
SELECT MAX(A.EFF_DATE)
FROM TABEL1 A, TABLE2 B, TABLE3 C
WHERE A.CO_ID = B.CO_ID AND
A.POL_ID = B.POL_ID AND
A.CVG_NUM = B.CVG_NUM AND
B.CLI_ID = C.CLI_ID AND
C.EFF_DATE <= A.PAY_OUT_DATE );
......
This is one hold file I created, I have another two to create, then create the report. User doesn't want to do it batch coz need to submit ticket every time.. Our online webfocus environment already connect to DB2 tables mentioned above...
SQL DB2
SELECT A.POL_ID, A.CO_ID, A.CVG_NUM,
B.CLI_ID,
C.CLI_FIRST_NM,
C.CLI_LAST_NM
FROM TABLE1 A, TABLE2 B, TABLE3 C
WHERE A.CO_ID = B.CO_ID AND
A.POL_ID = B.POL_ID AND
A.CVG_NUM = B.CVG_NUM AND
B.CLI_ID = C.CLI_ID AND
C.EFF_DATE = (
SELECT MAX(A.EFF_DATE)
FROM TABEL1 A, TABLE2 B, TABLE3 C
WHERE A.CO_ID = B.CO_ID AND
A.POL_ID = B.POL_ID AND
A.CVG_NUM = B.CVG_NUM AND
B.CLI_ID = C.CLI_ID AND
C.EFF_DATE <= A.PAY_OUT_DATE );
......
This is one hold file I created, I have another two to create, then create the report. User doesn't want to do it batch coz need to submit ticket every time.. Our online webfocus environment already connect to DB2 tables mentioned above...
RE: online webfocus max function?
CODE
SET SQLENGINE = DB2
SET WIDTH = 999
SET PANEL = 999
SET NODATA = ' '
-READ INPUTP &STRDT.I10
-READ INPUTP &ENDDT.I10
-READ INPUTP &PAYOTYP.A1
-READ INREGN &RGN.A5
-RUN
-*
SQL DB2
SELECT A.CO_ID, A.POL_ID, A.CVG_NUM,
B.INSRD_CLI_ID,
C.CLI_INDV_EFF_DT,
C.CLI_INDV_GR_CD,
C.ENTR_SUR_NM,
C.ENTR_GIV_NM,
C.CLI_INDV_SUR_NM,
C.CLI_INDV_GIV_NM,
C.CLI_INDV_MID_NM
FROM &RGN.TROPP A, &RGN.TCVGC B, &RGN.TCLNM C
WHERE A.CO_ID = B.CO_ID AND
A.POL_ID = B.POL_ID AND
A.CVG_NUM = B.CVG_NUM AND
B.CVG_CLI_REL_TYP_CD = 'P' AND
A.CO_ID = C.CO_ID AND
B.INSRD_CLI_ID = C.CLI_ID AND
C.CLI_INDV_EFF_DT =
(SELECT MAX(C.CLI_INDV_EFF_DT)
FROM &RGN.TROPP A, &RGN.TCVGC B, &RGN.TCLNM C
WHERE A.CO_ID = B.CO_ID AND
A.POL_ID = B.POL_ID AND
A.CVG_NUM = B.CVG_NUM AND
B.CVG_CLI_REL_TYP_CD = 'P' AND
A.CO_ID = C.CO_ID AND
B.INSRD_CLI_ID = C.CLI_ID AND
C.CLI_INDV_EFF_DT <=
A.ROP_PAYO_EFF_DT)
ORDER BY A.CO_ID, A.POL_ID, A.CVG_NUM;
TABLE FILE SQLOUT
PRINT *
ON TABLE HOLD AS TPNAME
END
-RUN
-*
SQL DB2
SELECT A.CO_ID, A.POL_ID, A.CVG_NUM, A.CVG_BNFT_NUM,
A.ROP_PAYO_EFF_DT, A.ROP_PAYO_SEQ_NUM,
CHAR(A.PREV_UPDT_TS) AS ROP_PROC_DATE,
A.PAYO_STAT_CD, A.ROP_PD_AMT, A.CAROVR_ROP_PD_AMT,
-* A.ROP_PD_RT, A.ROP_FORFT_RT,
B.ISS_EFF_DT,
( CASE
WHEN B.SUPP_BNFT_CD = 'J' THEN '1'
WHEN B.SUPP_BNFT_CD = 'S' THEN '3'
-* WHEN (A.ROP_PD_RT + A.ROP_FORFT_RT) < 1 THEN '3'
ELSE '2'
END ) AS OUTTYPE,
E.CVG_ORIG_CD
FROM &RGN.TROPP A , &RGN.TCVGB B , &RGN.TCVG E
WHERE A.CO_ID = B.CO_ID
AND A.POL_ID = B.POL_ID
AND A.CVG_NUM = B.CVG_NUM
AND A.CVG_BNFT_NUM = B.CVG_BNFT_NUM
AND A.CO_ID = E.CO_ID
AND A.POL_ID = E.POL_ID
AND A.CVG_NUM = E.CVG_NUM
ORDER BY A.CO_ID, A.POL_ID, A.CVG_NUM, A.CVG_BNFT_NUM;
TABLE FILE SQLOUT
PRINT *
ON TABLE HOLD AS TPROPP
END
-RUN
-*
SQL DB2
SELECT M.CO_ID, M.POL_ID, M.CVG_NUM,
M.ASSUM_CO_ID,
N.ETBL_DESC_TXT
FROM &RGN.TRI M, &RGN.TEDIT N
WHERE M.CO_ID = N.CO_ID AND
M.ASSUM_CO_ID = N.ETBL_VALU_ID AND
N.ETBL_TYP_ID = 'REIN' AND
N.ETBL_LANG_CD = 'E' AND
M.CSN_TRTY_TYP_CD = 'F'
ORDER BY M.CO_ID, M.POL_ID, M.CVG_NUM;
TABLE FILE SQLOUT
PRINT *
ON TABLE HOLD AS TPREIN
END
-RUN
-*
JOIN
TPROPP.CO_ID AND TPROPP.POL_ID AND TPROPP.CVG_NUM
IN TPROPP
TO TPNAME.CO_ID AND TPNAME.POL_ID AND TPNAME.CVG_NUM
IN TPNAME
AS J001
END
JOIN
TPROPP.CO_ID AND TPROPP.POL_ID AND TPROPP.CVG_NUM
IN TPROPP
TO TPREIN.CO_ID AND TPREIN.POL_ID AND TPREIN.CVG_NUM
IN TPREIN
AS J002
END
-*
DEFINE FILE TPROPP
-* DEFINE BEGIN BUSSRCE
BUSSRCE/A50 = IF TPROPP.CVG_ORIG_CD EQ '2'
THEN 'CHILD TERMRIDER CONVERSION' ELSE
IF TPROPP.CVG_ORIG_CD EQ 'E'
THEN 'CONVERSION OF TERM POLICY OR COVERAGE' ELSE
IF TPROPP.CVG_ORIG_CD EQ 'F'
THEN 'CONVERSION FROM FAMILY RIDER' ELSE
IF TPROPP.CVG_ORIG_CD EQ 'U'
THEN 'EXCHANGE' ELSE
IF TPROPP.CVG_ORIG_CD EQ '7'
THEN 'EXERCISING' ELSE
IF TPROPP.CVG_ORIG_CD EQ 'T'
THEN 'EXTERNAL' ELSE
IF TPROPP.CVG_ORIG_CD EQ 'Z'
THEN 'FULL CONVERSION' ELSE
IF TPROPP.CVG_ORIG_CD EQ 'Y'
THEN 'GROUP POLICY CONVERSION' ELSE
IF TPROPP.CVG_ORIG_CD EQ 'C'
THEN 'GUARANTEED ISSUED' ELSE
IF TPROPP.CVG_ORIG_CD EQ '6'
THEN 'GIR' ELSE
IF TPROPP.CVG_ORIG_CD EQ 'S'
THEN 'INTERNAL' ELSE
IF TPROPP.CVG_ORIG_CD EQ 'H'
THEN 'ISSUED UNDER BUSINESS PURCHASE OPTION' ELSE
IF TPROPP.CVG_ORIG_CD EQ 'G'
THEN 'ISSUED UNDER GIO' ELSE
IF TPROPP.CVG_ORIG_CD EQ '5'
THEN 'ISSUED UNDER SIB' ELSE
IF TPROPP.CVG_ORIG_CD EQ 'D'
THEN 'LIFE AND ANNUITY COMBO' ELSE
IF TPROPP.CVG_ORIG_CD EQ 'A'
THEN 'NEW BUSINESS - REGULAR' ELSE
IF TPROPP.CVG_ORIG_CD EQ 'B'
THEN 'NEW BUSINESS - SPECIAL' ELSE
IF TPROPP.CVG_ORIG_CD EQ 'W'
THEN 'NON-CONTRACTUAL CONVERSION' ELSE
IF TPROPP.CVG_ORIG_CD EQ 'X'
THEN 'OPTIMIZATION/UPGRADE' ELSE
IF TPROPP.CVG_ORIG_CD EQ '4'
THEN 'OEB' ELSE
IF TPROPP.CVG_ORIG_CD EQ '0'
THEN 'PARTIAL CONVERSION' ELSE
IF TPROPP.CVG_ORIG_CD EQ 'O'
THEN 'POLICY PLAN CHANGE' ELSE
IF TPROPP.CVG_ORIG_CD EQ '3'
THEN 'POLICY SPLIT' ELSE
IF TPROPP.CVG_ORIG_CD EQ 'R'
THEN 'REPLACEMENT' ELSE
IF TPROPP.CVG_ORIG_CD EQ 'K'
THEN 'REPLACEMENT OF EXISTING POLICY/COV' ELSE
IF TPROPP.CVG_ORIG_CD EQ '1'
THEN 'TERM CONVERSION' ELSE
IF TPROPP.CVG_ORIG_CD EQ 'V'
THEN 'TRANSFER' ELSE
IF TPROPP.CVG_ORIG_CD EQ 'I'
THEN 'VESTED ANNUITY' ELSE
IF TPROPP.CVG_ORIG_CD EQ 'N'
THEN 'NEW INSURANCE ISSUED IN INGENIUM' ELSE
'UNKNOWN';
REINSURER/A10 = SUBSTR (80,TPREIN.ETBL_DESC_TXT,1,10,10,REINSURER);
PRCYYYY/A4 = SUBSTR (26,TPROPP.ROP_PROC_DATE,1,4,4,PRCYYYY);
PRCMM/A2 = SUBSTR (26,TPROPP.ROP_PROC_DATE,6,7,2,PRCMM);
PRCDD/A2 = SUBSTR (26,TPROPP.ROP_PROC_DATE,9,10,2,PRCDD);
PRCDATE/A8YYMD = PRCYYYY | PRCMM | PRCDD;
PAYODESC/A25 = IF TPROPP.OUTTYPE EQ '1'
THEN 'DEATH ' ELSE
IF TPROPP.OUTTYPE EQ '2'
THEN 'EXPIRY OR EARLY SURRENDER' ELSE
'PARTIAL SURRENDER ';
STRDTTM/A10 = '&STRDT';
ENDDATE/YYMD = '&ENDDT';
STRDATE/YYMD = IF STRDTTM EQ ' '
THEN ENDDATE - 7 ELSE
'&STRDT';
PRCDATE1/YYMD = PRCDATE;
INSURED_NAM/A51 = TPNAME.ENTR_SUR_NM ||
(' ' | TPNAME.ENTR_GIV_NM);
CLNM_EFF/YYMD = TPNAME.CLI_INDV_EFF_DT;
-* DEFINE END BUSSRCE
END
OFFLINE
-*
TABLE FILE TPROPP
PRINT TPROPP.CO_ID NOPRINT
TPROPP.PAYO_STAT_CD NOPRINT
TPROPP.POL_ID AS 'POLICY,NUMBER'
TPROPP.CVG_NUM AS 'COVERAGE,NUMBER'
TPROPP.CVG_BNFT_NUM NOPRINT
TPROPP.ISS_EFF_DT AS 'ROP,ISSUE DATE'
PRCDATE AS 'PAYOUT,PROCESSING,DATE'
TPROPP.ROP_PAYO_EFF_DT AS 'PAYOUT,EFFEECTIVE,DATE'
TPROPP.ROP_PD_AMT/P17.2M AS 'ROP PAYOUT,AMOUNT'
TPROPP.CAROVR_ROP_PD_AMT/P17.2M AS 'CARRIED OVER,PAYOUT AMOUNT'
BUSSRCE AS 'SOURCE OF,BUSINESS'
TPNAME.INSRD_CLI_ID AS 'CLIENT ID'
INSURED_NAM AS 'INSURED,NAME'
CLNM_EFF AS 'NAME,EFFECTIVE'
-*TPROPP.ASSUM_CO_ID
REINSURER AS 'REINSURER'
-* PAYODESC AS 'PAYOUT,DESCRIPTION'
BY TPROPP.PAYODESC
NOPRINT
-* ROWS 'DEATH ' OVER
-* 'EXPIRY OR EARLY SURRENDER' OVER
-* 'PARTIAL SURRENDER '
-* SUBTOTAL AS 'SUBTOTAL ROP PAYOUT,'
BY TPROPP.CO_ID NOPRINT
BY TPROPP.POL_ID NOPRINT
BY TPROPP.CVG_NUM NOPRINT
BY TPROPP.CVG_BNFT_NUM NOPRINT
WHERE (TPROPP.PAYO_STAT_CD EQ '&PAYOTYP')
AND (PRCDATE1 GE STRDATE AND PRCDATE1 LE ENDDATE);
HEADING
"CI PAYOUT REPORT
<70 DATE: &DATETRMDYY <102 TIME: <108 &TOD </1"
"FOR THE PERIOD: <STRDATE TO <PRCDATE"
" "
ON TPROPP.PAYODESC SUBTOTAL AS 'SUBTOTAL ROP PAYOUT,'
ON TABLE COLUMN-TOTAL AS 'GRAND TOTAL : '
END
-RUN
-*
FIN
RE: online webfocus max function?
Also, you will need to make sure that the two files that you are reading at the begining are allocated before the -READ occurs.
What error messages are you getting?
RE: online webfocus max function?
RE: online webfocus max function?
Everything that you provided above looks like perfectly legitimate focus/webfocus code.
Make sure you take out the OFFLINE statment when running under webfocus though.