Hi,

I have a piece of webfocus code need to join a hold file with a table, it gives me out of sequence error. here is the code:

  
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

Here is the message when I run it:

  
 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

I know the join - J006 is giving me the trouble, if I take it out, it runs file, but ss you can see in the code, the hold file should be in join field's order. Any help? Thanks.

This message has been edited. Last edited by: <Kathryn Henning>,

Do you have field in the hold file indexed?

Here is the hold file:

  
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

I don't know how to index the hold file. I've used webfocus for less than 1 month.. But the hold file has all fields in BY clause. these 3 fields I'm using to join:

TPNAME.CO_ID
TPNAME.POL_ID
TPNAME.CVG_NUM

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

EDIT: My BAD, you have 3 records in TPNAMES, but multiple in MRFKROPP, many-to-one, I changed the JOIN...

This message has been edited. Last edited by: Tom Flynn,

ON TABLE HOLD AS WHATEVER FORMAT FOCUS INDEX FIELD

Tom, you caught that. It's many to one..

But I try to add key word 'ALL' or 'MULTIPLE' in the JOIN, it gives me unrecognized command error..

  
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

Yea, I was just about to write that..

TABLE out your J001 and J002 to a HOLD then JOIN the TPNAMES to that file; Just add that step...

quote:

Originally posted by Prarie:
ON TABLE HOLD AS WHATEVER FORMAT FOCUS INDEX FIELD

Isn't it if you have more than one field, you have to concat them into one field to index? Cos I'm joining it with a DB2 table, so I have to create a focus hold file for the db2 table as well?

Prarie logged off, so, I'll assist:

quote:

ON TABLE HOLD AS WHATEVER FORMAT FOCUS INDEX FIELD

ON TABLE HOLD AS WHATEVER FORMAT FOCUS INDEX FIELD1 FIELD2 FIELD3 FIELD4 etc.

The CAVEAT:

Column names cannot be more than 12 characters, 8 depending on the version of WF...

I don't think the issue is creating a FOCUS file as the hold, but I was wondering if the fact the actual BY are noprint may aggrevate the issue, I try not to join hold back to DB2 if I can help it, but rather create two hold files and join them of course don't know what platform they are running on. Out of sequence can be caused by many to one or one to many sometimes. Fighting that battle is no fun.

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.

Create two extracts one from the source file and the other from the target file
with a concaternated key and HOLD FORMAT FOCUS INDEX key on the second.

for example

DEFINE FILE SOURCE
FULLKEY/A20= KEY1 || KEY2 || KEY3;
END
TABLE FILE SOURCE
SUM S1 S2 S3
BY FULLKEY
BY KEY1
BY KEY2
BY KEY3
ON TABLE HOLD
END
-RUN
DEFINE FILE TARGET
FULLKEY/A20= KEY1 || KEY2 || KEY3;
END
TABLE FILE TARGET
PRINT T1 T2 T3
BY FULLKEY
ON TABLE HOLD AS JOINDATA FORMAT FOCUS INDEX FULLKEY
END
-RUN
JOIN CLEAR *
JOIN FULLKEY IN HOLD TO ALL FULLKEY IN JOINDATA AS J1

Remember if you have a 1 to many relation you must use print for the second request.

This message has been edited. Last edited by: <JG>,

Or if this complex concatenation does not work, you can look for the option to use MATCH.

with MATCH you can "join" on more than one key.

I'm almost certain that your problem is caused by the fact that the hold file and the table your trying to join is not in the same sequence!!!! So to be able to do this join make sure that the records in the holdfile are in the same order as the records in the table you're trying to join.

You can solve this by creating an indexed focus file as suggested bij JG or as Frank suggested by using the MATCH command!

It's up to you.

Hope this helps.

In this situation, I would always use MATCH FILE and in this particular situation, performance should be much better than joining to a flat file.

Also, JG states:

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.

.

I haven't tried it, but FOCUS and XFOCUS file have the ability to have multi-field indexes. See the link.

http://documentation.informationbuilders.com/masterinde.../71snf/rep_lang4.htm

XFOCUS you have to pay for.

Building the MDI index requires an extra step so would be
an extra overhead.

Also if you are joining to a compound key and it is in an indexed focus db
then your'e not joining to a flat file.

Performance is going to be largly dependant on volumes and the larger the volumes
the less efficient MATCH is because of it's use of FOCSORT.

Followed JG's suggestion and it works. I guess the key is indexed hold files. Thanks..
I see the statement 'JOIN CLEAR *' in the example, what does it do? How many join can you have at the same time? I read old document for 5.xx it say 16, same as 7.x?

JOIN CLEAR * clears the JOIN structure. I always issue them when I am finished with the JOIN. I also clear FILEDEFs for temp files when I'm finished with those. I have found that in a large .fex, that cleaning up as I go eliminates problems.

Going back to the original error message ... in my case (joining two temporary tables (files in WF speak) on several fields, concatenated into one computed field to join on) I got the error message when I did not have the BY clauses in the same order for both tables. Apparently WF only wants to join tables when they are already sorted the same.

not necessarily marc,
ON TABLE HOLD FORMAT XFOCUS INDEX FIELDA AND FIELDB AND...
use multidim indices... makes your JOIN life so happy

But xfocus is not default available I think. I thought it is a separate license.
Other option is SQL format. All you need is access to an SQL server.

the fields you join should have the same format(length)

SO before you join make sure two fields are of same format

If they are not of same format in the hold create a virtual in DEFINE or using COMPUTE command.