Hi I m trying the index the HOLD File and i m getting errors as below,

(FOC205) THE DESCRIPTION CANNOT BE FOUND FOR FILE NAMED: GL_TRANS_TEMP BYPASSING TO END OF COMMAND
0 ERROR AT OR NEAR LINE 147 IN PROCEDURE ADHOCRQ FOCEXEC *
(FOC205) THE DESCRIPTION CANNOT BE FOUND FOR FILE NAMED: bunit_mstracc
(FOC205) THE DESCRIPTION CANNOT BE FOUND FOR FILE NAMED: BUNIT_MSTRACC
BYPASSING TO END OF COMMAND

The code of the report goes as below,
JOIN Tables
END
-*-------------------
TABLE FILE F_GL_TRANS
PRINT
MSTR_ACCT_KEY AS MASTER_ACCT
BUS_UNIT_KEY AS BUS_UNIT
F_GL_TRANS.F_GL_TRANS.AMT
COMPUTE DUMMY/A20 = ' ';
GL_TRANS_DETAILS.GL_TRANS_DETAILS.REMARK
GL_TRANS_DETAILS.GL_TRANS_DETAILS.EXPLANATION
-*
BY MSTR_CHART_OF_ACCTS.MSTR_CHART_OF_ACCTS.OBJECT_ACCT_CD
BY MSTR_CHART_OF_ACCTS.MSTR_CHART_OF_ACCTS.ACCT_DESC
BY BUS_UNIT.BUS_UNIT.BUS_UNIT_ID
BY COMPANY.COMPANY.COMPANY_ID
-*
WHERE F_GL_TRANS.F_GL_TRANS.GL_DATE GE DT(&TRDATE1) AND F_GL_TRANS.F_GL_TRANS.GL_DATE LE DT(&TRDATE2);
WHERE COMPANY.COMPANY.COMPANY_ID EQ '&PROPNBR1';
-*ON TABLE HOLD AS GL_TRANS_TEMP FORMAT FOCUS
ON TABLE HOLD AS GL_TRANS_TEMP FORMAT FOCUS INDEX MASTER_ACCT BUS_UNIT
-**************************** This is where I m getting error. Earlier I did not have index on this Hold file 'GL_TRANS_TEMP'
-*****************************But now I indexed 2 fields MASTER_ACCT and BUS_UNIT
END
-RUN
-*-------
TABLE FILE GL_TRANS_TEMP
BY MASTER_ACCT
BY BUS_UNIT
BY OBJECT_ACCT_CD
ON TABLE HOLD AS BUNIT_MSTRACC FORMAT FOCUS INDEX MASTER_ACCT BUS_UNIT
END
-*-------------------
JOIN
LEFT_OUTER BUNIT_MSTRACC.MASTER_ACCT AND BUNIT_MSTRACC.BUS_UNIT IN BUNIT_MSTRACC TO MULTIPLE
F_GL_BALANCES.MASTER_ACCT_KEY AND F_GL_BALANCES.BUS_UNIT_KEY IN F_GL_BALANCES AS JX1
END /////------- this is line # 147
TABLE FILE BUNIT_MSTRACC
SUM
COMPUTE BEGIN_BAL/D20.2 = IF OBJECT_ACCT_CD LE '399999' THEN END_OF_PER_BALANCE + PRIOR_YEAR_BALANCE ELSE END_OF_PER_BALANCE;
BY BUS_UNIT
BY MASTER_ACCT
BY OBJECT_ACCT_CD
WHERE END_OF_MONTH_DATE EQ DT(&BALEDDT);
WHERE LEDGER_TYPE_KEY EQ 1;
ON TABLE HOLD AS GL_BALANCES FORMAT FOCUS INDEX MASTER_ACCT BUS_UNIT
END
-EXIT


Gauri
WF 7.6.2 - Oracle

Your focus format file should be a smaller file name (less or equal 8) and your index fields should also be a lot smaller.

See if that works.

Frank,
I resized the variables upto 8 or less,
but does not seem to be working.
It says the same error at line # 147.

The message indicates that it cannot find the master file descriptions for your GL_TRANS_TEMP file.

quote:

(FOC205) THE DESCRIPTION CANNOT BE FOUND FOR FILE NAMED: GL_TRANS_TEMP BYPASSING TO END OF COMMAND

Since it cannot find that one, it cannot create the file you need for the join.

If GL_TRANS_TEMP is a FOCUS file, put an APP PREPENDPATH in your program to point to the directory where your master is. Then in the master, put a DATASET parameter to point to the .foc file

If it is a relational table, put an APP PREPENDPATH in your program to point to the directory where your master is.

If it is a sequential file, do the path thing and put an APP FI in the program to point to the file.

Once you are passed that, we can work on the join.

ON TABLE HOLD AS GL_TRANS_TEMP FORMAT FOCUS INDEX MASTER_ACCT BUS_UNIT
-**************************** This is where I m getting error. Earlier I did not have index on this Hold file 'GL_TRANS_TEMP'
-*****************************But now I indexed 2 fields MASTER_ACCT and BUS_UNIT
END
-RUN

Try putting an -EXIT after your 1st -RUN and trying it again.

Does it work? If NO, then comment out the HOLD FILE name;

Does this work? Meaning are you at least getting data?

If YES, is there any reason why you have quotes around 'GL_TRANS_TEMP' ? Or is it just wrapping that comment to the next line? The one I'm asking about is the one right after your comment???

Is it possible that your comment is getting bitten by the 80 char limit width bug? I've had some issues with Developer Studio still having a problem with any chars exceeding 80 chars in width. I never tried that with v7.6 so it may be something else. If anything, try removing the comment if it's still causing a problem.

Once it looks like it's working, then you can try putting your hold file back then just doing a one recordlimit test for the next step without holding it. If that's not working, then it must be the app pathing for your hold files.

This message has been edited. Last edited by: cmallain,

1)  TABLE FILE F_GL_TRANS
2)  PRINT
3)  MSTR_ACCT_KEY AS MASTER_ACCT
4)  BUS_UNIT_KEY AS BUS_UNIT
5)  F_GL_TRANS.F_GL_TRANS.AMT
...
13) ON TABLE HOLD AS GL_TRANS_TEMP FORMAT FOCUS INDEX MASTER_ACCT BUS_UNIT
14) END

The last line of code you refer to a two index fields, but these fields are no real fields.

I would change this in

3)  MSTR_ACCT_KEY AS 'MSTR'
4)  BUS_UNIT_KEY AS 'BUNIT'
13) ON TABLE HOLD AS GL_TRANS_TEMP FORMAT FOCUS INDEX MAMSTR_ACCT_KEY BUS_UNIT_KEY

The keys in the hold file you refer to will show up with their "AS" names.

The AS Name is not recognized until after the hold is completely created. Try indexing on the db field name.

Unless they changed things in v7.6 the index field name has to be 12 characters or less. Try doing a define and renaming your fields ACCTKEY and UNITKEY

Ginny,
Since a Hold file is a of focus format, I did the option 1, to put APP PREPENDPATH, but doesnt seem to be working.
Basically if I dont index the GL_TRANS_TEMP hold file, it works fine but dosnt give me correct amounts.


cmallain,
The comment isnt there is in the report, I have added it for better understading,.....so thats not an issue.


FrankDutch,
I have put ASNAMES and still not working.


Linus,
tried your option too.....but bad luck for me

Pat,
I renamed all the fields and here is my new code now,

JOIN
LEFT_OUTER F_GL_TRANS.MSTR_ACCT_KEY IN F_GL_TRANS TO UNIQUE
MSTR_CHART_OF_ACCTS.MASTER_ACCT_KEY IN MSTR_CHART_OF_ACCTS AS J4
END
JOIN
LEFT_OUTER F_GL_TRANS.BUS_UNIT_KEY IN F_GL_TRANS TO UNIQUE
BUS_UNIT.BUS_UNIT_KEY IN BUS_UNIT AS J5
END
JOIN
LEFT_OUTER F_GL_TRANS.LEDGER_TYPE_KEY IN F_GL_TRANS TO UNIQUE
LEDGER_TYPE.LEDGER_TYPE_KEY IN LEDGER_TYPE AS J6
END
JOIN
LEFT_OUTER F_GL_TRANS.GL_TRANS_DETAIL_KEY IN F_GL_TRANS TO UNIQUE
GL_TRANS_DETAILS.GL_TRANS_DETAIL_KEY IN GL_TRANS_DETAILS AS J11
END
JOIN
LEFT_OUTER F_GL_TRANS.BATCH_KEY IN F_GL_TRANS TO UNIQUE
BATCH.BATCH_KEY IN BATCH AS J12
END
JOIN
BUS_UNIT.COMPANY_KEY IN F_GL_TRANS TO UNIQUE
COMPANY.COMPANY_KEY IN COMPANY AS J0
END
-*-------------------
TABLE FILE F_GL_TRANS
PRINT
MSTR_ACCT_KEY AS ACCTKEY
BUS_UNIT_KEY AS UNITKEY
F_GL_TRANS.F_GL_TRANS.AMT
-*
BY MSTR_CHART_OF_ACCTS.OBJECT_ACCT_CD
BY MSTR_CHART_OF_ACCTS.ACCT_DESC
BY BUS_UNIT.BUS_UNIT_ID
BY COMPANY.COMPANY_ID
-*
WHERE FF_GL_TRANS.GL_DATE GE DT(&TRDATE1) AND F_GL_TRANS.GL_DATE LE DT(&TRDATE2);
WHERE COMPANY.COMPANY_ID EQ '&PROPNBR1';
-*
ON TABLE HOLD AS GLTRANS FORMAT FOCUS INDEX ACCTKEY UNITKEY
-*----------
-*************Comment : As per FrankDutch suggestions, reduces trhe size of index fields and also Accodint ************
-*************to Linus suggestiosn changed these index fields to Db field names, but it didnt work either. ************
-*----------
END
-RUN
-*-------
TABLE FILE GLTRANS
BY ACCTKEY
BY UNITKEY
BY OBJECT_ACCT_CD
ON TABLE HOLD AS BU_ACC FORMAT FOCUS INDEX ACCTKEY UNITKEY
END
-RUN
-*-EXIT
-*-------------------
JOIN
LEFT_OUTER BU_ACC.ACCTKEY AND BU_ACC.UNITKEY IN BU_ACC TO MULTIPLE
F_GL_BALANCES.MASTER_ACCT_KEY AND F_GL_BALANCES.BUS_UNIT_KEY IN F_GL_BALANCES AS JX1
END
TABLE FILE BU_ACC
SUM
COMPUTE BEGIN_BAL/D20.2 = IF OBJECT_ACCT_CD LE '399999' THEN
END_OF_PER_BALANCE + PRIOR_YEAR_BALANCE
ELSE END_OF_PER_BALANCE;
BY UNITKEY
BY ACCTKEY
BY OBJECT_ACCT_CD
WHERE END_OF_MONTH_DATE EQ DT(&BALEDDT);
WHERE LEDGER_TYPE_KEY EQ 1;
ON TABLE HOLD AS GLBAL FORMAT FOCUS INDEX ACCTKEY UNITKEY
END
-RUN
-*----------
-************* I also need to join the 2 HOLD files 'GLTRANS'(created first) and GLBAL(created above) ************
-************* both the joining HOLD Files are focus format and indexed by same fields ************
-************* Till I put -EXIT heere, it runs well, so looks like there is a problem in the following join*******
-************* but unbale to get the problem/syntax error.
-*----------
-*---------------------------------
JOIN
INNER GLTRANS.ACCTKEY AND GLTRANS.UNITKEY IN GLTRANS TO
GLBAL.ACCTKEY AND GLBAL.UNITKEY IN GLBAL AS JX2
END
-*------------------------
TABLE FILE GLTRANS
PRINT
COMPANY_ID AS 'CO'
OBJECT_ACCT_CD AS 'Account'
COMPUTE DEBIT/D20.2CM = IF AMT GE 0 THEN AMT ELSE 0;
AS 'Debit'
COMPUTE CREDIT/D20.2CBM = IF AMT LT 0 THEN AMT ELSE 0;
AS 'Credit'
END
BY COMPANY_ID NOPRINT
BY OBJECT_ACCT_CD NOPRINT


Its still giving me en error as
-------------------------------------------------------------------------
(FOC236) LINKED FILE DOES NOT HAVE A MATCHING KEY FIELD OR SEGMENT: GLBAL
BYPASSING TO END OF COMMAND
-------------------------------------------------------------------------

Gauri,

It finally dawned on me what your problem is. When joining FOCUS files together, you may only join on one field. (I just read that this was changing, but I don't remember what release it is in).

What you have to do when you create the files is concatenate the ACCTKEY and the UNITKEY together in a DEFINE, call it GLKEY or something like that. Do that for both host and target. Then use that field to do the join.

An example of one of your requests would be:

DEFINE FILE F_GL_TRANS
GLKEY/Ann=ACCTKEY|UNITKEY;
END
TABLE FILE F_GL_TRANS
PRINT
MSTR_ACCT_KEY AS ACCTKEY
BUS_UNIT_KEY AS UNITKEY
F_GL_TRANS.F_GL_TRANS.AMT
-*
BY GLKEY
BY MSTR_CHART_OF_ACCTS.OBJECT_ACCT_CD
BY MSTR_CHART_OF_ACCTS.ACCT_DESC
BY BUS_UNIT.BUS_UNIT_ID
BY COMPANY.COMPANY_ID
-*
WHERE FF_GL_TRANS.GL_DATE GE DT(&TRDATE1) AND F_GL_TRANS.GL_DATE LE DT(&TRDATE2);
WHERE COMPANY.COMPANY_ID EQ '&PROPNBR1';
-*
ON TABLE HOLD AS GLTRANS FORMAT FOCUS INDEX GLKEY

Do that for both and then join using GLKEY. You still have ACCTKEY and UNITKEY as discreet fields if you need them.

Note: Please replace Ann with the combined length of the two key fields.

The host file can reference more than one field in the join but the cross referenced file must only reference one, so only the fields in the CR file need to be joined. Here is the explanation from the manual:

When you are joining two FOCUS data sources you can specify up to four alphanumeric fields in the host file that, if concatenated, contain values shared with the cross-referenced file. You may not specify more than one field in the cross-referenced file when the suffix of the cross-referenced file is FOC. For example, assume the cross-referenced file contains a phone number field having an area-code-prefix-exchange format. The host file has an area-code field, a prefix field and an exchange field. You can specify these three fields to join them to the phone number field in the cross-referenced file. The JOIN command treats these fields as one. Only FOCUS databases have this restriction. If the cross referenced file had the phone number split apart in three fields you would need to join them together into one field to be able to join on the phone number.