How can I join 2 files and get the proper output I want. File1 has job number and multiple entries for an invoice number, file2 has a job number and multiple entries for sales rep(for split commission purposes).
file1 has job1, invoice1; job1 invoice2; job2, invoice1; etc.
File2 has job1, slsrep1; job1 slsrep2; no job2; etc.

I want to join these files so I have this result:
job1 invoice1 slsrep1, then
job1 invoice1 slsrep2, then
job1 invoice2 slsrep1, then
job1 invoice2 slsrep2, then
job2 invoice1 and empty slsrep#(no split commission).

everything I have tried does not work or i get the following error message
FOC1072 DUPLICATES IN FILE2

Jenny

show us the complete fex and maybe your masters

did you consider MATCH instead of JOIN?

Frank

I sometime join on just job number to all job number (in my case students). Do a print * and hold then manipulae, but as Frank says masters and fex help.

Jenny,
It looks like you are joining 2 flat files. If the files are not too big, I would suggest you load them in to FOCUS files. You shouldn't have any problems then.

Jenny, what you're seeing is typical when you're joining flat file to flat file there it's a many-to-many relationship. The technique I've always employed is to take the second file and hold it off in FORMAT FOCUS, and index it by the key field. That way, you have one flat file and one FOCUS file. Being that they are different, FOCUS will not actually try to join them together like it does with two flat files, but instead it will read the first record in FILE1, then find all the associated records in FILE2, then read the second record in FILE1, then find all the associated records in FILE2, etc., etc. If the files are very large, this may not be very efficient and as was already suggested, a MATCH may work better for you. Below is my attempt to give you a sample:

TABLE FILE FILE2
PRINT SLSREP
BY JOB
ON TABLE HOLD FORMAT FOCUS INDEX JOB
END
-RUN
JOIN JOB IN FILE1 TO ALL JOB IN FILE2 AS J1
TABLE FILE FILE1
PRINT INVOICE SLSREP
BY JOB
END
-RUN

Good luck.

I agree with the approach Jessica (and Danny) is taking, loading one file into a FOCUS db with an INDEX would keep things simple.

Disagree though with Jessica saying that a JOIN on an INDEX in a FOCUS file is inefficient. This type of access is very efficient and is normally much better than a MATCH.

Would be interested in the MFDs, FEX and maybe more data, as a JOIN between 2 flat files, which I assume you are using because of the error, can be made to work to give you exactly what you want.

Clarification: I said my suggestion MAY not be very efficient, not that it was inefficient. I was allowing for the possibility that this may not work well for Jenny in her situation. I would always try that first before resorting to using a MATCH and it's always worked well for me.

I have tried reversing the join, I have tried a match and neither work well. I will try creating the focus format and see what that gives me. It is another option. I am relatively new to WebFocus and am learning trial by fire! Thanks for the feedback, and I will let you all know what the outcome is. Thanks again.

[QUOTE]Originally posted by Jim_at_LM:
Jim, I did that stepping through idea and it is a GREAT idea. My join was actually working properly, however.......What is happening is this.

When I join file 1 and file 2 the join works great. This joined flat file has information for the job(file1) and commission percentages(file2) for 3 different reps(which I have to parse into separate records for each rep and also compute $$ using the job informationfrom (file 1). For sake of example on job 12345 total commission is $5000 rep1 gets 1/2 of the commission, rep2 gets 1/4 and rep3 gets 1/4. When I take the joined file and try to parse out the info for rep1,(2500), rep2(1250) rep3(1250), first of all the calculations are correct as I step through at the end of each report. The big problem is the appends is not working correctly. I will put the code in below so you can see what I am doing. It appears that it is writing the 1st file for rep1 then overwriting for rep2 and appending rep3. It does not make sense to me but when I "view source" there is a message that says Format conversion error(foc1346) on the computed fields. However that error is not there when I view the file immediately prior to the appends section. HSTINV2 is the joined file and it joins properly. When I view HSTINV3 the computes are figuring correctly. Also the field definitions are exactly the same. any ideas?

TABLE FILE HSTINV2
PRINT
DATINV
BILTYP
CUSTNO
TOTAMT
INNSAL
JOBTYP
CURSLS
PROGRM
CREP1
CPERC1
CFRC1A
CFRC1B
CREP2
CPERC2
CFRC2A
CFRC2B
CREP3
CPERC3
CFRC3A
CFRC3B
CRMRKS
TOTCOM
SCGTCA
ADDSRV
SCMISC
SCTTCA
COMPUTE CHRG0/A1 = IF CREP1 EQ 0 THEN 'Y' ELSE '';
COMPUTE CHRG1/A1 = IF CREP1 NE 0 THEN 'N' ELSE '';
COMPUTE CHRG2/A1 = IF CREP3 NE 0 THEN 'N' ELSE IF CREP3 EQ 0 AND CREP2 NE 0 THEN 'Y' ELSE ' ';
COMPUTE CHRG3/A1 = IF CREP3 NE 0 THEN 'Y' ELSE ' ';
COMPUTE SVCOMM/D13.2 = IF SCGTCA NE 0 THEN SCGTCA ELSE IF PROGRM EQ 'W' AND DATINV LE '20060602' THEN SCTTCA ELSE SCTTCA + ADDSRV;
COMPUTE COMMSN0/D13.2 = IF CREP1 EQ 0 AND CURSLS NE 0 THEN SVCOMM ELSE 0;
COMPUTE MISC0/D13.2 = IF CREP1 EQ 0 AND CURSLS NE 0 THEN SCMISC ELSE 0;
COMPUTE PRICE0/D13.2 = IF CREP1 EQ 0 AND CURSLS NE 0 THEN TOTAMT - INNSAL ELSE 0;
COMPUTE COMMSN1/D13.2 = IF CREP1 EQ 0 THEN 0 ELSE IF CPERC1 NE 0 THEN ((SVCOMM * CPERC1) /100) ELSE IF CFRC1A NE 0 THEN (SVCOMM / (CFRC1B * CFRC1A)) ELSE 0;
COMPUTE MISC1/D13.2 = IF CREP1 EQ 0 THEN 0 ELSE IF CPERC1 NE 0 THEN ((SCMISC * CPERC1) / 100) ELSE IF CFRC1A NE 0 THEN (SCMISC / (CFRC1B * CFRC1A)) ELSE 0;
COMPUTE PRICE1/D13.2 = IF CREP1 EQ 0 THEN 0 ELSE IF CPERC1 NE 0 THEN (((TOTAMT - INNSAL) * CPERC1) / 100) ELSE IF CFRC1A NE 0 THEN ((TOTAMT - INNSAL) / (CFRC1B * CFRC1A)) ELSE 0;
COMPUTE COMMSN2/D13.2 = IF CREP2 EQ 0 THEN 0 ELSE IF CPERC2 NE 0 THEN ((SVCOMM * CPERC2) /100) ELSE IF CFRC2A NE 0 THEN (SVCOMM / (CFRC2B * CFRC2A)) ELSE 0;
COMPUTE COMMSN2T/D13.2 = IF CREP2 EQ 0 THEN 0 ELSE IF ((COMMSN1 + COMMSN2) NE SVCOMM) THEN (SVCOMM - COMMSN1) ELSE COMMSN2;
COMPUTE MISC2/D13.2 = IF CREP2 EQ 0 THEN 0 ELSE IF CPERC2 NE 0 THEN ((SCMISC * CPERC2) / 100) ELSE IF CFRC2A NE 0 THEN (SCMISC / (CFRC2B * CFRC2A)) ELSE 0;
COMPUTE MISC2T/D13.2 = IF CREP2 EQ 0 THEN 0 ELSE IF ((MISC1 + MISC2) NE SCMISC) THEN (SCMISC - MISC1) ELSE MISC2;
COMPUTE PRICE2/D13.2 = IF CREP2 EQ 0 THEN 0 ELSE IF CPERC2 NE 0 THEN (((TOTAMT - INNSAL) * CPERC2) / 100) ELSE IF CFRC2A NE 0 THEN ((TOTAMT - INNSAL) / (CFRC2B * CFRC2A)) ELSE 0;
COMPUTE PRICE2T/D13.2 = IF CREP2 EQ 0 THEN 0 ELSE IF ((PRICE1 + PRICE2) NE (TOTAMT - INNSAL)) THEN ((TOTAMT - INNSAL) - PRICE1) ELSE PRICE2;
COMPUTE COMMSN3/D13.2 = IF CREP3 NE 0 THEN 0 ELSE IF CPERC3 EQ 0 THEN ((SVCOMM * CPERC3) /100) ELSE IF CFRC3A NE 0 THEN (SVCOMM / (CFRC3B * CFRC3A)) ELSE 0;
COMPUTE COMMSN3T/D13.2 = IF CREP3 EQ 0 THEN 0 ELSE IF ((COMMSN1 + COMMSN2 + COMMSN3) NE SVCOMM) THEN (SVCOMM - COMMSN1- COMMSN2) ELSE COMMSN3;
COMPUTE MISC2/D13.2 = IF CREP3 EQ 0 THEN 0 ELSE IF CPERC3 NE 0 THEN ((SCMISC * CPERC3) / 100) ELSE IF CFRC3A NE 0 THEN (SCMISC / (CFRC3B * CFRC3A)) ELSE 0;
COMPUTE MISC3T/D13.2 = IF CREP3 EQ 0 THEN 0 ELSE IF ((MISC1 + MISC2) + MISC3) NE SCMISC THEN ((SCMISC - MISC1) - MISC2) ELSE MISC3;
COMPUTE PRICE3/D13.2 = IF CREP3 EQ 0 THEN 0 ELSE IF CPERC3 NE 0 THEN (((TOTAMT - INNSAL) * CPERC3) / 100) ELSE IF CFRC3A NE 0 THEN ((TOTAMT - INNSAL) / (CFRC3B * CFRC3A)) ELSE 0;
COMPUTE PRICE3T/D13.2 = IF CREP3 EQ 0 THEN 0 ELSE IF (((PRICE1 + PRICE2) + PRICE3) NE (TOTAMT - INNSAL)) THEN (((TOTAMT - INNSAL) - PRICE1) - PRICE2) ELSE PRICE3;
BY JOBNO
BY INVCNO
BY JOBSEQ
BY SCRVNO
WHERE ( TOTCOM NE 0 ) OR ( SCGTCA NE 0 ) OR ( ADDSRV NE 0 ) OR ( SCMISC NE 0 ) OR ( SCTTCA NE 0 );
ON TABLE HOLD AS HISTINV3 FORMAT ALPHA
END
-* SELECT OUT THE CURRENT DATE RANGE RECORDS
TABLE FILE HISTINV3
PRINT
INVCNO
JOBSEQ
SCRVNO
DATINV
BILTYP
CUSTNO
TOTAMT
INNSAL
JOBTYP
CURSLS
PROGRM
CREP1
CPERC1
CFRC1A
CFRC1B
CREP2
CPERC2
CFRC2A
CFRC2B
CREP3
CPERC3
CFRC3A
CFRC3B
CRMRKS
TOTCOM
SCGTCA
ADDSRV
SCMISC
SCTTCA
SVCOMM
CHRG0
CHRG1
CHRG2
CHRG3
COMMSN0
MISC0
PRICE0
COMMSN1
MISC1
PRICE1
COMMSN2T
MISC2T
PRICE2T
COMMSN3T
MISC3T
PRICE3T
BY JOBNO
ON TABLE HOLD AS HISTINV4A FORMAT ALPHA
END
TABLE FILE HISTINV4A
PRINT *
ON TABLE HOLD AS HISTINV4
END
-*SPLIT INTO SEPARATE RECORDS SO YOU CAN FIGURE THE COMMISSION ON THE JOB
FILEDEF YBKSPLITCOM1 DISK YBKSPLITCOM1.FTM (APPEND
TABLE FILE HISTINV4
PRINT
INVCNO
JOBSEQ
SCRVNO
DATINV
BILTYP
CUSTNO
TOTAMT
INNSAL
JOBTYP
CURSLS
PROGRM
TOTCOM
SCGTCA
ADDSRV
SCMISC
SCTTCA
SVCOMM
COMPUTE SPLTCOM/A1 = '';
COMPUTE CHARGD/A1 = CHRG0;
COMPUTE CREP/D3 = CURSLS;
COMPUTE CPERC/D2 = 0;
COMPUTE CFRACA/D1 = 0;
COMPUTE CFRACB/D1 = 0;
COMPUTE COMMT/D14.2 = IF CREP1 EQ 0 AND CURSLS NE 0 THEN COMMSN0 ELSE 0;
COMPUTE MISCT/D14.2 = IF CREP1 EQ 0 AND CURSLS NE 0 THEN MISC0 ELSE 0;
COMPUTE PRICET/D14.2 = IF CREP1 EQ 0 AND CURSLS NE 0 THEN PRICE0 ELSE 0;
BY JOBNO
WHERE CREP1 EQ 0 AND CURSLS NE 0;
ON TABLE HOLD AS YBKSPLITCOM1
END
TABLE FILE HISTINV4
PRINT
INVCNO
JOBSEQ
SCRVNO
DATINV
BILTYP
CUSTNO
TOTAMT
INNSAL
JOBTYP
CURSLS
PROGRM
TOTCOM
SCGTCA
ADDSRV
SCMISC
SCTTCA
SVCOMM
COMPUTE SPLTCOM/A1 = '*';
COMPUTE CHARGD/A1 = CHRG1;
COMPUTE CREP/D3 = CREP1;
COMPUTE CPERC/D2 = CPERC1;
COMPUTE CFRACA/D1 = CFRC1A;
COMPUTE CFRACB/D1 = CFRC1B;
COMPUTE COMMT/D14.2 = IF CREP1 NE 0 THEN COMMSN1 ELSE 0;
COMPUTE MISCT/D14.2 = IF CREP1 NE 0 THEN MISC1 ELSE 0;
COMPUTE PRICET/D14.2 = IF CREP1 NE 0 THEN PRICE1 ELSE 0;
BY JOBNO
WHERE CREP1 NE 0;
ON TABLE HOLD AS YBKSPLITCOM1 FORMAT ALPHA
END
TABLE FILE HISTINV4
PRINT
INVCNO
JOBSEQ
SCRVNO
DATINV
BILTYP
CUSTNO
TOTAMT
INNSAL
JOBTYP
CURSLS
PROGRM
TOTCOM
SCGTCA
ADDSRV
SCMISC
SCTTCA
SVCOMM
COMPUTE SPLTCOM/A1 = '*';
COMPUTE CHARGD/A1 = CHRG2;
COMPUTE CREP/D3 = CREP2;
COMPUTE CPERC/D2 = CPERC2;
COMPUTE CFRACA/D1 = CFRC2A;
COMPUTE CFRACB/D1 = CFRC2B;
COMPUTE COMMT/D12.2 = IF CREP2 NE 0 THEN COMMSN2T ELSE 0;
COMPUTE MISCT/D12.2 = IF CREP2 NE 0 THEN MISC2T ELSE 0;
COMPUTE PRICET/D12.2 = IF CREP2 NE 0 THEN PRICE2T ELSE 0;
BY JOBNO
WHERE CREP2 NE 0;
ON TABLE HOLD AS YBKSPLITCOM1 FORMAT ALPHA
END
TABLE FILE HISTINV4
PRINT
INVCNO
JOBSEQ
SCRVNO
DATINV
BILTYP
CUSTNO
TOTAMT
INNSAL
JOBTYP
CURSLS
PROGRM
TOTCOM
SCGTCA
ADDSRV
SCMISC
SCTTCA
SVCOMM
COMPUTE SPLTCOM/A1 = '*';
COMPUTE CHARGD/A1 = CHRG3;
COMPUTE CREP/D3 = CREP3;
COMPUTE CPERC/D2 = CPERC3;
COMPUTE CFRACA/D1 = CFRC3A;
COMPUTE CFRACB/D1 = CFRC3B;
COMPUTE COMMT/D12.2 = IF CREP3 NE 0 THEN COMMSN3T ELSE 0;
COMPUTE MISCT/D12.2 = IF CREP3 NE 0 THEN MISC3T ELSE 0;
COMPUTE PRICET/D12.2 = IF CREP3 NE 0 THEN PRICE3T ELSE 0;
BY JOBNO
WHERE CREP3 NE 0;
ON TABLE HOLD AS YBKSPLITCOM1 FORMAT ALPHA
END
TABLE FILE YBKSPLITCOM1
PRINT
INVCNO
JOBSEQ
SCRVNO
DATINV
BILTYP
JOBTYP
CUSTNO
SPLTCOM
CHARGD
SCGTCA
COMPUTE CURRENTREP/D3 = IF CREP NE 0 THEN CREP ELSE CURSLS;
COMPUTE CURSLSREP/P3 = IF CREP NE 0 THEN CREP ELSE CURSLS;
COMMT
MISCT
PRICET
COMPUTE COMMSN/D13.2 = IF COMMT NE 0 THEN COMMT ELSE 0;
COMPUTE MISC/D13.2 = IF MISCT NE 0 THEN MISCT ELSE 0;
COMPUTE PRICE/D13.2 = IF PRICET NE 0 THEN PRICET ELSE 0;
BY JOBNO
ON TABLE HOLD AS YBKSPLITCOM2 FORMAT ALPHA
END
TABLE FILE YBKSPLITCOM2
PRINT *
END
-EXIT

Jenny

A quick look through, and I wonder why you have:
ON TABLE HOLD AS YBKSPLITCOM1
in the first request after issuing the FILEDEF and:
ON TABLE HOLD AS YBKSPLITCOM1 FORMAT ALPHA
for the subsequent requests. These are different file layouts.

Also, the first
COMPUTE SPLTCOM/A1 = '';
appears to not have a space between the 2 quotes.

Try changing those 2 and see what happens.

[QUOTE]Originally posted by Alan B:
OH MY what an idiot! Yes, those are different. An extra set of eyes is nice to have. It appears that I get past the first format error but now I get this still. < !--
0 NUMBER OF RECORDS IN TABLE= 14 LINES= 14
0 NUMBER OF RECORDS IN TABLE= 14 LINES= 14
0 NUMBER OF RECORDS IN TABLE= 14 LINES= 14
0 NUMBER OF RECORDS IN TABLE= 14 LINES= 14
0 NUMBER OF RECORDS IN TABLE= 14 LINES= 14
0 NUMBER OF RECORDS IN TABLE= 11 LINES= 11
0 NUMBER OF RECORDS IN TABLE= 3 LINES= 3
0 NUMBER OF RECORDS IN TABLE= 3 LINES= 3
0 NUMBER OF RECORDS IN TABLE= 0 LINES= 0
(FOC1346) : FORMAT ERROR: Record 12 , Column 218
(FOC1130) FORMAT CONVERSION ERROR FIELD/KEY : PRICET
(FOC1346) : FORMAT ERROR: Record 13 , Column 218
(FOC1130) FORMAT CONVERSION ERROR FIELD/KEY : PRICET
(FOC1346) : FORMAT ERROR: Record 14 , Column 218
(FOC1130) FORMAT CONVERSION ERROR FIELD/KEY : PRICET
0 NUMBER OF RECORDS IN TABLE= 14 LINES= 14
0 NUMBER OF RECORDS IN TABLE= 14 LINES= 14

WebFOCUS Version 7.1.4 compiled and linked on Fri May 19 16:45:40 EDT 2006 (Gen branch714:284)
-->

HERE IS THE NEW AND IMPROVED VERSION.


-*SPLIT INTO SEPARATE RECORDS SO YOU CAN FIGURE THE COMMISSION ON THE JOB
FILEDEF YBKSPLITCOM1 DISK YBKSPLITCOM1.FTM (APPEND
TABLE FILE HISTINV4
PRINT
INVCNO
JOBSEQ
SCRVNO
DATINV
BILTYP
CUSTNO
TOTAMT
INNSAL
JOBTYP
CURSLS
PROGRM
TOTCOM
SCGTCA
ADDSRV
SCMISC
SCTTCA
SVCOMM
COMPUTE SPLTCOM/A1 = ' ';
COMPUTE CHARGD/A1 = CHRG0;
COMPUTE CREP/D3 = CURSLS;
COMPUTE CPERC/D2 = 0;
COMPUTE CFRACA/D1 = 0;
COMPUTE CFRACB/D1 = 0;
COMPUTE COMMT/D14.2 = IF CREP1 EQ 0 AND CURSLS NE 0 THEN COMMSN0 ELSE 0;
COMPUTE MISCT/D14.2 = IF CREP1 EQ 0 AND CURSLS NE 0 THEN MISC0 ELSE 0;
COMPUTE PRICET/D14.2 = IF CREP1 EQ 0 AND CURSLS NE 0 THEN PRICE0 ELSE 0;
BY JOBNO
WHERE CREP1 EQ 0 AND CURSLS NE 0;
ON TABLE HOLD AS YBKSPLITCOM1 FORMAT ALPHA
END
TABLE FILE HISTINV4
PRINT
INVCNO
JOBSEQ
SCRVNO
DATINV
BILTYP
CUSTNO
TOTAMT
INNSAL
JOBTYP
CURSLS
PROGRM
TOTCOM
SCGTCA
ADDSRV
SCMISC
SCTTCA
SVCOMM
COMPUTE SPLTCOM/A1 = '*';
COMPUTE CHARGD/A1 = CHRG1;
COMPUTE CREP/D3 = CREP1;
COMPUTE CPERC/D2 = CPERC1;
COMPUTE CFRACA/D1 = CFRC1A;
COMPUTE CFRACB/D1 = CFRC1B;
COMPUTE COMMT/D14.2 = IF CREP1 NE 0 THEN COMMSN1 ELSE 0;
COMPUTE MISCT/D14.2 = IF CREP1 NE 0 THEN MISC1 ELSE 0;
COMPUTE PRICET/D14.2 = IF CREP1 NE 0 THEN PRICE1 ELSE 0;
BY JOBNO
WHERE CREP1 NE 0;
ON TABLE HOLD AS YBKSPLITCOM1 FORMAT ALPHA
END
TABLE FILE HISTINV4
PRINT
INVCNO
JOBSEQ
SCRVNO
DATINV
BILTYP
CUSTNO
TOTAMT
INNSAL
JOBTYP
CURSLS
PROGRM
TOTCOM
SCGTCA
ADDSRV
SCMISC
SCTTCA
SVCOMM
COMPUTE SPLTCOM/A1 = '*';
COMPUTE CHARGD/A1 = CHRG2;
COMPUTE CREP/D3 = CREP2;
COMPUTE CPERC/D2 = CPERC2;
COMPUTE CFRACA/D1 = CFRC2A;
COMPUTE CFRACB/D1 = CFRC2B;
COMPUTE COMMT/D12.2 = IF CREP2 NE 0 THEN COMMSN2T ELSE 0;
COMPUTE MISCT/D12.2 = IF CREP2 NE 0 THEN MISC2T ELSE 0;
COMPUTE PRICET/D12.2 = IF CREP2 NE 0 THEN PRICE2T ELSE 0;
BY JOBNO
WHERE CREP2 NE 0;
ON TABLE HOLD AS YBKSPLITCOM1 FORMAT ALPHA
END
TABLE FILE HISTINV4
PRINT
INVCNO
JOBSEQ
SCRVNO
DATINV
BILTYP
CUSTNO
TOTAMT
INNSAL
JOBTYP
CURSLS
PROGRM
TOTCOM
SCGTCA
ADDSRV
SCMISC
SCTTCA
SVCOMM
COMPUTE SPLTCOM/A1 = '*';
COMPUTE CHARGD/A1 = CHRG3;
COMPUTE CREP/D3 = CREP3;
COMPUTE CPERC/D2 = CPERC3;
COMPUTE CFRACA/D1 = CFRC3A;
COMPUTE CFRACB/D1 = CFRC3B;
COMPUTE COMMT/D12.2 = IF CREP3 NE 0 THEN COMMSN3T ELSE 0;
COMPUTE MISCT/D12.2 = IF CREP3 NE 0 THEN MISC3T ELSE 0;
COMPUTE PRICET/D12.2 = IF CREP3 NE 0 THEN PRICE3T ELSE 0;
BY JOBNO
WHERE CREP3 NE 0;
ON TABLE HOLD AS YBKSPLITCOM1 FORMAT ALPHA
END
TABLE FILE YBKSPLITCOM1
PRINT
INVCNO
JOBSEQ
SCRVNO
DATINV
BILTYP
JOBTYP
CUSTNO
SPLTCOM
CHARGD
SCGTCA
COMPUTE CURRENTREP/D3 = IF CREP NE 0 THEN CREP ELSE CURSLS;
COMPUTE CURSLSREP/P3 = IF CREP NE 0 THEN CREP ELSE CURSLS;
COMMT
MISCT
PRICET
COMPUTE COMMSN/D13.2 = IF COMMT NE 0 THEN COMMT ELSE 0;
COMPUTE MISC/D13.2 = IF MISCT NE 0 THEN MISCT ELSE 0;
COMPUTE PRICE/D13.2 = IF PRICET NE 0 THEN PRICET ELSE 0;
BY JOBNO
ON TABLE HOLD AS YBKSPLITCOM2 FORMAT ALPHA
END
TABLE FILE YBKSPLITCOM2
PRINT *
END
-EXIT

JIM,

Nevermind, I am still a dufus, one is defined 12.2 and one is 14.2. I am so glad you guys are out there to help us less experienced goofuses! Let me fix that and see

ok, it seems like when I make a change, more errors come up. I was not having a problem on the other fields and now they are bombing with format errors.

I have a related query

In file 1 i have-
Rollno name day
------- ---- -----
111 aaa mon
111 aaa tue

FIle 2 contains-

Rollno comments
----- --------
111 present
111 absent

Both are flat files
So i hold it in format focus and then join

'join Rollno in file1 to all Rollno in file2'

but it is giving-
Rollno name day comments
------- ---- ---- --------
111 aaa mon present
111 aaa mon absent
111 aaa tue present
111 aaa tue absent

And if i dont use 'all' the it gives-
Rollno name day comments
------- ---- ---- --------
111 aaa mon present
111 aaa tue present

but i want it as-
Rollno name day comments
------- ---- ---- --------
111 aaa mon present
111 aaa tue absent

How do i make sure that the 1st record in file 1 joins to the corresponding 1st record in file 2,
and the 2nd record of file1 joins with the corresponding 2nd record with file 2 ?
Any suggestions?

Manash,
Before you hold in format focus and join the two files, you can create a define field for each file that is a unique row counter (ROWNUM/I9 = ROWNUM + 1.
Then join on this new field.
That way you get a one to one join for each row.
Hope this is what you wanted to do....

Manash

It is a bit confusing to come up with an other question.
try posting this as a new problem, and I (or someone else) will answer there.

I am not sure if this helps anyone, but I had the same issue with the FOC1346 error... and the issue was the FORMAT ALPHA... I got rid of it and I didn't get those format errors anymore.

Hope that helps a bit.