online webfocus max function?

[jackyzsn]

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...

 

[jackyzsn]

offline(batch mode JCL) in full, works in JCL, doesn't work in online(using IExplorer)

TSO SQL SET SSID DB0A
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

 

[gizzy17]

Just checking, but you do have an OFFLINE command just after your DEFINE ends.

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?

 

[jackyzsn]

The JCL above is for Focus - the environment installed in our mainframe. We also have Webfocus environment installed, it connects to the same DB2 tables. Actually, I don't really know how to convert above code into webfocus code..

 

[gizzy17]

I don't see any JCL that you provided above.

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.