SET EMPTYREPORT = ON SET NODATA = ' ' JOIN MRFKROPP.MRFKROPP.CO_ID AND MRFKROPP.MRFKROPP.POL_ID AND MRFKROPP.MRFKROPP.CVG_NUM IN MRFKROPP TO MRFKCVGC.MRFKCVGC.CO_ID AND MRFKCVGC.MRFKCVGC.POL_ID AND MRFKCVGC.MRFKCVGC.CVG_NUM IN MRFKCVGC AS J003 END JOIN MRFKCVGC.MRFKCVGC.CO_ID AND MRFKCVGC.MRFKCVGC.INSRD_CLI_ID IN MRFKROPP TO MRFKCLNM.MRFKCLNM.CO_ID AND MRFKCLNM.MRFKCLNM.CLI_ID IN MRFKCLNM AS J004 END TABLE FILE MRFKROPP PRINT MRFKROPP.MRFKROPP.CO_ID MRFKROPP.MRFKROPP.POL_ID MRFKROPP.MRFKROPP.CVG_NUM MRFKCVGC.MRFKCVGC.CVG_CLI_REL_TYP_CD MRFKCLNM.MRFKCLNM.CLI_ID MRFKCLNM.MRFKCLNM.CLI_INDV_EFF_DT AS NAM_EFF_DATE MRFKCLNM.MRFKCLNM.ENTR_SUR_NM MRFKCLNM.MRFKCLNM.ENTR_GIV_NM MRFKCLNM.MRFKCLNM.CLI_INDV_SUR_NM MRFKCLNM.MRFKCLNM.CLI_INDV_GIV_NM BY MRFKROPP.MRFKROPP.CO_ID BY MRFKROPP.MRFKROPP.POL_ID BY MRFKROPP.MRFKROPP.CVG_NUM WHERE (MRFKCVGC.MRFKCVGC.CVG_CLI_REL_TYP_CD EQ 'P') AND (MRFKCLNM.MRFKCLNM.CLI_INDV_EFF_DT LE MRFKROPP.MRFKROPP.ROP_PAYO_EFF_DT) AND MRFKCLNM.MRFKCLNM.CLI_INDV_GR_CD EQ 'AL'; ON TABLE NOTOTAL ON TABLE HOLD AS TPNAME END TABLE FILE TPNAME SUM TPNAME.CO_ID TPNAME.POL_ID TPNAME.CVG_NUM TPNAME.CLI_INDV_EFF_DT TPNAME.ENTR_SUR_NM TPNAME.ENTR_GIV_NM TPNAME.CLI_INDV_SUR_NM TPNAME.CLI_INDV_GIV_NM BY TPNAME.CO_ID NOPRINT BY TPNAME.POL_ID NOPRINT BY TPNAME.CVG_NUM NOPRINT BY HIGHEST CLI_INDV_EFF_DT NOPRINT ON TABLE NOTOTAL ON TABLE HOLD AS TPNAMES END JOIN MRFKROPP.MRFKROPP.CO_ID AND MRFKROPP.MRFKROPP.POL_ID AND MRFKROPP.MRFKROPP.CVG_NUM AND MRFKROPP.MRFKROPP.CVG_BNFT_NUM IN MRFKROPP TO MRFKCVGB.MRFKCVGB.CO_ID AND MRFKCVGB.MRFKCVGB.POL_ID AND MRFKCVGB.MRFKCVGB.CVG_NUM AND MRFKCVGB.MRFKCVGB.CVG_BNFT_NUM IN MRFKCVGB TAG J001 AS J001 END JOIN MRFKROPP.MRFKROPP.CO_ID AND MRFKROPP.MRFKROPP.POL_ID AND MRFKROPP.MRFKROPP.CVG_NUM IN MRFKROPP TO MRFKCVG.MRFKCVG.CO_ID AND MRFKCVG.MRFKCVG.POL_ID AND MRFKCVG.MRFKCVG.CVG_NUM IN MRFKCVG TAG J002 AS J002 END JOIN MRFKROPP.MRFKROPP.CO_ID AND MRFKROPP.MRFKROPP.POL_ID AND MRFKROPP.MRFKROPP.CVG_NUM IN MRFKROPP TO TPNAMES.CO_ID AND TPNAMES.POL_ID AND TPNAMES.CVG_NUM IN TPNAMES TAG J006 AS J006 END DEFINE FILE MRFKROPP -* DEFINE BEGIN PROCYYYY PROCYYYY/A4 = SUBSTR ( 26, MRFKROPP.PREV_UPDT_TS , 1 , 4 , 4 , PROCYYYY); -* DEFINE END PROCYYYY -* DEFINE BEGIN PROCMM PROCMM/A2 = SUBSTR ( 26, MRFKROPP.PREV_UPDT_TS , 6 , 7 , 2 ,PROCMM); -* DEFINE END PROCMM -* DEFINE BEGIN PROCDD PROCDD/A2 = SUBSTR ( 26, MRFKROPP.PREV_UPDT_TS , 9 ,10 ,24 , PROCDD); -* DEFINE END PROCDD -* DEFINE BEGIN PROCDATE PROCDATE/A8YYMD = PROCYYYY | PROCMM | PROCDD; PRCDATE1/YYMD = PROCDATE; -* DEFINE END PROCDATE -* DEFINE BEGIN BUSSRCE BUSSRCE/A40 = IF MRFKCVG.CVG_ORIG_CD EQ '2' THEN 'CHILD TERM RIDER CONVERSION' ELSE IF MRFKCVG.CVG_ORIG_CD EQ 'E' THEN 'CONVERSION OF TERM POLICY OR COVERAGE' ELSE IF MRFKCVG.CVG_ORIG_CD EQ 'F' THEN 'CONVERSION FROM FAMILY RIDER' ELSE IF MRFKCVG.CVG_ORIG_CD EQ 'U' THEN 'EXCHANGE' ELSE IF MRFKCVG.CVG_ORIG_CD EQ '7' THEN 'EXERCISING FIO' ELSE IF MRFKCVG.CVG_ORIG_CD EQ 'T' THEN 'EXTERNAL' ELSE IF MRFKCVG.CVG_ORIG_CD EQ 'Z' THEN 'FULL CONVERSION' ELSE IF MRFKCVG.CVG_ORIG_CD EQ 'Y' THEN 'GROUP POLICY CONVERSION' ELSE IF MRFKCVG.CVG_ORIG_CD EQ 'C' THEN 'GUARANTEED ISSUE - NO EVIDENCE' ELSE IF MRFKCVG.CVG_ORIG_CD EQ '6' THEN 'GIR' ELSE IF MRFKCVG.CVG_ORIG_CD EQ 'S' THEN 'INTERNAL' ELSE IF MRFKCVG.CVG_ORIG_CD EQ 'H' THEN 'ISSUED UNDER BUSINESS PURCHASE OPTION' ELSE IF MRFKCVG.CVG_ORIG_CD EQ 'G' THEN 'ISSUED UNDER GIO' ELSE IF MRFKCVG.CVG_ORIG_CD EQ '5' THEN 'ISSUED UNDER SIB' ELSE IF MRFKCVG.CVG_ORIG_CD EQ 'D' THEN 'LIFE AND ANNUITY COMBO - NO EVIDENCE' ELSE IF MRFKCVG.CVG_ORIG_CD EQ 'A' THEN 'NEW BUSINESS - REGULAR' ELSE IF MRFKCVG.CVG_ORIG_CD EQ 'B' THEN 'NEW BUSINESS - SPECIAL' ELSE IF MRFKCVG.CVG_ORIG_CD EQ 'W' THEN 'NON-CONTRACTUAL CONVERSION' ELSE IF MRFKCVG.CVG_ORIG_CD EQ 'X' THEN 'OPTIMIZATION/UPGRADE' ELSE IF MRFKCVG.CVG_ORIG_CD EQ '4' THEN 'OEB' ELSE IF MRFKCVG.CVG_ORIG_CD EQ '0' THEN 'PARTIAL CONVERSION' ELSE IF MRFKCVG.CVG_ORIG_CD EQ 'O' THEN 'POLICY PLAN CHANGE' ELSE IF MRFKCVG.CVG_ORIG_CD EQ '3' THEN 'POLICY SPLIT' ELSE IF MRFKCVG.CVG_ORIG_CD EQ 'R' THEN 'REPLACEMENT' ELSE IF MRFKCVG.CVG_ORIG_CD EQ 'K' THEN 'REPLACEMENT OF EXISTING POLICY/COV' ELSE IF MRFKCVG.CVG_ORIG_CD EQ '1' THEN 'TERM CONVERSION' ELSE IF MRFKCVG.CVG_ORIG_CD EQ 'V' THEN 'TRANSFER' ELSE IF MRFKCVG.CVG_ORIG_CD EQ 'I' THEN 'VESTED ANNUITY' ELSE IF MRFKCVG.CVG_ORIG_CD EQ 'N' THEN 'NEW INSURANCE ISSUED IN INGENIUM' ELSE 'UNKNOWN'; -* DEFINE END BUSSRCE -* DEFINE BEGIN OUTTYPE OUTTYPE/A1 = IF MRFKCVGB.SUPP_BNFT_CD EQ 'J' THEN '1' ELSE IF (MRFKROPP.MRFKROPP.ROP_PD_RT + MRFKROPP.MRFKROPP.ROP_FORFT_RT ) LT 1 THEN '3' ELSE '2'; -* DEFINE END OUTTYPE -* DEFINE BEGIN PAYDESC PAYDESC/A26 = IF OUTTYPE EQ '1' THEN 'Death ' ELSE IF OUTTYPE EQ '2' THEN 'Expiry and Early Surrender' ELSE 'Partial Surrender '; -* DEFINE END PAYDESC STRDATE/YYMD = '&STRDT'; ENDDATE/YYMD = '&ENDDT'; PDRT/D6.2 = (MRFKROPP.MRFKROPP.ROP_PD_RT * 100); FORFTRT/D6.2 = (MRFKROPP.MRFKROPP.ROP_FORFT_RT * 100); -*UPAYOTYP/A1=UPCASE(&PAYOTYPE.LENGTH,&PAYOTYPE,'A1'); OUTDESC/A28 = IF MRFKROPP.MRFKROPP.PAYO_STAT_CD EQ 'A' THEN 'Return of Premium Payouts ' ELSE 'Return of Premiums Reversals'; END TABLE FILE MRFKROPP PRINT MRFKROPP.MRFKROPP.CO_ID MRFKROPP.MRFKROPP.PAYO_STAT_CD MRFKROPP.MRFKROPP.POL_ID MRFKROPP.MRFKROPP.CVG_NUM MRFKROPP.MRFKROPP.CVG_BNFT_NUM J001.MRFKCVGB.ISS_EFF_DT PROCDATE MRFKROPP.MRFKROPP.ROP_PAYO_EFF_DT MRFKROPP.MRFKROPP.ROP_PD_AMT MRFKROPP.MRFKROPP.CAROVR_ROP_PD_AMT BUSSRCE PAYDESC STRDATE ENDDATE OUTDESC PDRT FORFTRT TPNAMES.ENTR_SUR_NM TPNAMES.ENTR_GIV_NM WHERE (PRCDATE1 GT STRDATE AND PRCDATE1 LE ENDDATE); BY MRFKROPP.MRFKROPP.CO_ID NOPRINT BY MRFKROPP.MRFKROPP.PAYO_STAT_CD NOPRINT BY MRFKROPP.MRFKROPP.POL_ID NOPRINT BY MRFKROPP.MRFKROPP.CVG_NUM NOPRINT BY MRFKROPP.MRFKROPP.CVG_BNFT_NUM NOPRINT ON TABLE HOLD END -* TABLE FILE HOLD HEADING "CI Payout Adhoc Report " "" "FOR THE PERIOD: <E13 TO <E14" "DATE: &DATETRMDYY TIME: &TOD " PRINT E03 AS 'Policy,Number' E04 AS 'Coverage,Number' E06 AS 'ROP Issue,Date' E07 AS 'Payout,Processing Date' E08 AS 'Payout,Effective Date' E09/P17.2M AS 'ROP Payout,Amount' E10/P17.2M AS 'Carried Over,Payout Amount' E16/D6.2% AS 'Payout,%' E17/D6.2% AS 'Forfeited,%' E11 AS 'Source of,Business' E18 E19 WHERE E02 = 'A'; BY E01 NOPRINT BY E02 NOPRINT BY E12 NOPRINT SUBTOTAL AS 'Subtotal ROP Payouts -' BY E03 NOPRINT BY E04 NOPRINT BY E05 NOPRINT ON E02 SUBHEAD "<E15" ON E12 SUBHEAD "" "ROP Payout - <E12" ON TABLE SET STYLE * TYPE= HEADING,LINE=1,JUSTIFY=CENTER,$ TYPE= HEADING,LINE=1,OBJECT=TEXT,ITEM=1,STYLE=ITALIC+BOLD,SIZE=14,$ -*TYPE= HEADING,LINE=3,SIZE=10,$ TYPE= HEADING,LINE=4,JUSTIFY=RIGHT,SIZE=10, $ TYPE= SUBTOTAL,STYLE=ITALIC+BOLD,$ TYPE= GRANDTOTAL,STYLE=ITALIC+BOLD,$ TYPE= SUBHEAD,STYLE=BOLD,$ TYPE= TITLE,STYLE=UNDERLINE,$ ENDSTYLE ON TABLE SET HTMLCSS OFF ON TABLE COLUMN-TOTAL E09 E10 END
0 NUMBER OF RECORDS IN TABLE= 6 LINES= 6 0 NUMBER OF RECORDS IN TABLE= 6 LINES= 3 (FOC1070) VALUE FOR JOIN 'FROM' FIELD OUT OF SEQUENCE. RETRIEVAL ENDED (FOC205) THE DESCRIPTION CANNOT BE FOUND FOR FILE NAMED: HOLD BYPASSING TO END OF COMMAND (FOC205) THE DESCRIPTION CANNOT BE FOUND FOR FILE NAMED: HOLD BYPASSING TO END OF COMMAND
In Focus since 1993. WebFOCUS 7.7.03 Win 2003 |
TABLE FILE TPNAME SUM TPNAME.CO_ID TPNAME.POL_ID TPNAME.CVG_NUM TPNAME.CLI_INDV_EFF_DT TPNAME.ENTR_SUR_NM TPNAME.ENTR_GIV_NM TPNAME.CLI_INDV_SUR_NM TPNAME.CLI_INDV_GIV_NM BY TPNAME.CO_ID NOPRINT BY TPNAME.POL_ID NOPRINT BY TPNAME.CVG_NUM NOPRINT BY HIGHEST CLI_INDV_EFF_DT NOPRINT ON TABLE NOTOTAL ON TABLE HOLD AS TPNAMES END
quote:
JOIN
ALL
MRFKROPP.MRFKROPP.CO_ID AND MRFKROPP.MRFKROPP.POL_ID AND MRFKROPP.MRFKROPP.CVG_NUM
IN MRFKROPP
TO TPNAMES.CO_ID AND TPNAMES.POL_ID AND TPNAMES.CVG_NUM
IN TPNAMES TAG J006
AS J006
END
In Focus since 1993. WebFOCUS 7.7.03 Win 2003 |
0 NUMBER OF RECORDS IN TABLE= 6 LINES= 6 0 NUMBER OF RECORDS IN TABLE= 6 LINES= 3 0 ERROR AT OR NEAR LINE 81 IN PROCEDURE ADHOCRQ FOCEXEC * (FOC376) SYNTAX ERROR OR MISSING ELEMENT IN JOIN/COMBINE COMMAND: (FOC1517) UNRECOGNIZED COMMAND IN MRFKROPP (FOC1517) UNRECOGNIZED COMMAND TO TPNAMES.CO_ID AND TPNAMES.POL_ID AND TPNAMES.CVG_NUM (FOC1517) UNRECOGNIZED COMMAND IN TPNAMES TAG J006 (FOC1517) UNRECOGNIZED COMMAND AS J006 0 ERROR AT OR NEAR LINE 201 IN PROCEDURE ADHOCRQ FOCEXEC * (FOC003) THE FIELDNAME IS NOT RECOGNIZED: TPNAMES.ENTR_SUR_NM BYPASSING TO END OF COMMAND (FOC009) INCOMPLETE REQUEST STATEMENT (FOC205) THE DESCRIPTION CANNOT BE FOUND FOR FILE NAMED: HOLD BYPASSING TO END OF COMMAND (FOC205) THE DESCRIPTION CANNOT BE FOUND FOR FILE NAMED: HOLD BYPASSING TO END OF COMMAND
quote:Originally posted by Prarie:
ON TABLE HOLD AS WHATEVER FORMAT FOCUS INDEX FIELD
quote:
ON TABLE HOLD AS WHATEVER FORMAT FOCUS INDEX FIELD
Frank | |
prod: WF 7.6.10 platform Windows, databases: msSQL2000, msSQL2005, RMS, Oracle, Sybase,IE7 test: WF 7.6.10 on the same platform and databases,IE7 |
.quote:The principle issue you have is that you cannot do a multi field join to focus data sources
or flat file data sources and for Focus you need an index.
In Focus since 1979///7706m/5 ;wintel 2008/64;OAM security; Oracle db, ///MRE/BID |
Frank | |
prod: WF 7.6.10 platform Windows, databases: msSQL2000, msSQL2005, RMS, Oracle, Sybase,IE7 test: WF 7.6.10 on the same platform and databases,IE7 |