After banging my head on this for two day I have a suspicion of my problem.

I'm trying to join two focus hold files. I need to use two indexed fields each and I have tried to use "JOIN bla1 AND bla2 IN file1 TO bla1 AND bla2 IN file2 AS join1". Needless to say I've tried about every type of join and formatting to get it to work, however no success. After RT*M I realized that the AND in a JOIN is concatenating fields and the fields I'm using are P7. Is this my problem? The fields are in a non-concatable format. I would have thought that the function is converting it to alpha to concatenate in the background or doing something else but I guess that would require extra processing. Am I wrong? Do I have some other problem? I've found many work arounds but I would like a sanity check too.

Any thoughts?

trob,

You have two problems, actually. You may specify up to 4 fields on the host side of the join (from), as long as they are all alphanumeric. However, you may apecify only one field on the target side, if the target file is a FOCUS file. In other words, you can join A and B and C and D in File 1 to X in File 2, if A, B, C and D are all alphanumeric, and their concatenated format is the same as the format of X. But you can't join A, B, C and D to W, X, Y and Z if the latter set of fields is in a FOCUS file. You can do that if it's a relational database, but not if it's FOCUS.

I doubt the and is concatinating fields, anyway in my world of mostly DB2, the join a and b in filea to a and b in fileb, indicates the join would result in a set of information where a in filea equals a in fileb and b in filea equals b in file b. Are you getting errors? Are the two fields you are trying to join formatted the same? If they are all P7 then the format isn't the issue. Are the fields keys, that makes a difference in some data base types. Focus hold files like to have keys. I rarely hold as format focus, I ususally hold as hold with the default whatever, as long as the files are sorted the same no issues. Any other issues you are having?

Hmmmmm..... I just noticed that you said Focus HOLD files. That implies that the suffix is FIX. On the other hand, if the suffix is FIX, you would not have indexes. So I'm a little confused. Anyway, if these are flat files, then you can disregard that target side limitation. Your only problem would be the numeric field. And I'm not even sure about that, really. I pretty much never join flat files.

To prevent repeating the same issues every day, it might be helpful to first search on this site.
The point is that this issue is brought up today so please take a look there.

Thanks Leah and dwf.

Frank, do you realize the effort you made in posting your comment you could have actually contributed something useful. I've been searching and reading the forum for two days now and their is nothing that fully explains using JOIN and it's concatenation method. Yes I see they have a similar problem but after working on this I was wanting some reassurance of my problem. Oh but wait, after reading your post there I realize all you have to contribute is a less efficient match to work around. And I think jackyzsn said "I guess the key is indexed hold files", I think I mentioned my hold files are indexed. Next time I will start my own thread again instead of hi-jacking another. Thanks.

dwf: I don't follow you on the suffix is FIX thing. Do you have that backwards? You can index focus hold files however you can not index flat text files.

Leah: The W7 Keysheet Quick Reference Guide states that using the AND in a JOIN concatenates the fields. Using the GUI it will not allow me to select both fields and drag them to the target file. However I can code it that way then go back to the GUI and its there. The only problem is that when I run the code I get a master file error on the host file.

Today I'm using a define for my hold files to create a new concatenated field. I'll index it on the target and try to use it for the match. I guess this is the most efficient work around.

For smaller volumes of data you can use WHERE based join to get the same logical answer.
< Assume there is a DB called carseat which was created as SUM XRC BY COUNTRY BY SEATS ;
Now you want to join this to CAR matching on COUNTRY and SEATS;
JOIN FILE CAR AT SEATS TO ALL FILE CARSEAT AT COUNTRY
WHERE ORIGIN.COUNTRY EQ SEG01.COUNTRY ;
WHERE SEATS EQ SEG01.SEATS;
END

TABLE FILE CAR PRINT RC XRC COMPUTE RAT/D6.3=RC/XRC; BY COUNTRY BY CAR BY SEATS
END

Hi trob,

This is the working code after help from here, the report is generated properly..

Hope it helps..

  
-* HTML Tool
-* Created by Report Assistant
-* FF Line do not change this line! Field Name
-* FF Line do not change this line! Alias
-* FF Line do not change this line! Format
-* FF Line do not change this line! Segment
-* FF Line do not change this line! ShowFieldTree
-* FF Line do not change this line! displayTree=0
-* J001 J001
-* J002 J002
-*SET &UPAYOTYP=UPCASE(&PAYOTYPE.LENGTH,&PAYOTYPE,'A1')
SET EMPTYREPORT = ON
SET NODATA = ' '
-*SET ALL = ON
-*
-*
-*
-*         CREATE HOLE FILE HAS NAME
-*
-*
-*
-*
-*
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
DEFINE FILE TPNAME
TPNMK/A14 = TPNAME.CO_ID || TPNAME.POL_ID || TPNAME.CVG_NUM
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 TPNMK
BY TPNAME.CO_ID
BY TPNAME.POL_ID
BY TPNAME.CVG_NUM
BY HIGHEST TPNAME.CLI_INDV_EFF_DT
ON TABLE NOTOTAL
ON TABLE HOLD AS TPNAMES FORMAT FOCUS INDEX TPNMK
END
-RUN
-*
-*
-*
-*     
-*     CREATE ROPP HOLD FILE
-*
-*
-*
-*
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
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  '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);
TPRPK/A14 = MRFKROPP.MRFKROPP.CO_ID || MRFKROPP.MRFKROPP.POL_ID || MRFKROPP.MRFKROPP.CVG_NUM;
-*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
TPRPK
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 AS TPROPP FORMAT FOCUS INDEX TPRPK
END
-RUN
-*
-*
-*
-*
-*       JOIN ROPP HOLD FILE AND NAME HOLD FILE
-*
-*
-*
-*
JOIN CLEAR *
JOIN TPRPK IN TPROPP
TO TPNMK IN TPNAMES 
AS J006
END
-*
TABLE FILE TPROPP
HEADING
"CI Payout Adhoc Report "
""
"FOR THE PERIOD: " 
"DATE: &DATETRMDYY   TIME: &TOD  "
PRINT 
TPROPP.CO_ID
TPROPP.POL_ID
TPROPP.CVG_NUM
TPROPP.TPRPK
TPROPP.ROP_PAYO_EFF_DT
TPROPP.ROP_PD_AMT
TPROPP.BUSSRCE
TPNAMES.CLI_INDV_SUR_NM
TPNAMES.CLI_INDV_GIV_NM
ON TABLE SET HTMLCSS OFF
ON TABLE COLUMN-TOTAL  
END
-*
-* End Report Assistant

Thanks Brian for your suggestion. I was thinking that in a WHERE based JOIN you could only base your WHERE statement on the host file. I see the way you using the SEG01 to call the host after join so I'll give that a try too. I like that better than my previously mentioned define base work around. I'll post my results later.

Trob

quote:

I've been searching and reading the forum for two days now and their is nothing that fully explains using JOIN and it's concatenation method.

Are you sure?
I'm reading this site for almost a year now and since the beginning of this year I try to help people with their problems.
I use Focus for almost 20 years and I do learn however almost every day new facts. Beneat this website their are manuals to explain about hold files, formats, joins, match etc.

A focus hold file is e.g. not a format FIX.

Joining focus files with multiple keys kan not be done, so you have to create your own new key by concatenating the original real keys. And thatfore you sometimes have to convert numbers to strings.

An other solution is the use of match, but that might influence your performance.

BTW would you be so kind to update your signature?

Thanks for the helpful input Frank. I've got a few manuals and after some detail reading I realized the AND is concatenating the fields. It would be a nice feature if the fields where not alpha, it would go ahead and convert in the background to concatenate.

I was a little lost by the format FIX remark, thanks for clearing that up too.

trob,

As I read you initial post, it was not perfectly clear whether you had held your file in FORMAT FOCUS or not. If you do not add that phrase to the HOLD statement, FOCUS creates a flat hold file, with SUFFIX=FIX. And you can join flat files, in spite of the fact that there are no indexes. That just have to be identically sorted.

Trob,

I grab my fields one at a time in the gui to do joins. Documentation sometimes uses verbiage that sometimes distorts. Have you tried XTRIEVAL = OFF to look at what gets generated? You should see sometimes what WebFOCUS thinks I should join on.

I'm happy to hear you have resolved your issue. As they say there is more than one way to skin a cat.