I had an issue previously with output of a report as the result of a Left_Outer Join not behaving due to Multiple Records in the From File. I resolved this issue by using a format focus index file. My issue now is I need to modify this program and append to the Index File or find a way to do this. I created a crude representation using the CAR File:

 -* File IndexFiledef.fex
FILEDEF PASS1 DISK PASS1.TXT
-RUN
TABLE FILE CAR
PRINT
     'CAR.ORIGIN.COUNTRY'
     'CAR.COMP.CAR'
     'CAR.CARREC.MODEL'
     'CAR.BODY.DEALER_COST'
     'CAR.BODY.RETAIL_COST'
WHERE CAR.ORIGIN.COUNTRY EQ 'ENGLAND';
ON TABLE HOLD AS PASS1 
-*FORMAT FOCUS INDEX 'CAR.COMP.CAR'
END
FILEDEF PASS1 DISK PASS1.TXT (APPEND
-RUN
TABLE FILE CAR
PRINT
     'CAR.ORIGIN.COUNTRY'
     'CAR.COMP.CAR'
     'CAR.CARREC.MODEL'
     'CAR.BODY.DEALER_COST'
     'CAR.BODY.RETAIL_COST'
WHERE CAR.ORIGIN.COUNTRY EQ 'JAPAN';
ON TABLE HOLD AS PASS1 
-*FORMAT FOCUS INDEX 'CAR.COMP.CAR'
END
TABLE FILE PASS1
PRINT *
END 
-EXIT
 

This code works fine and it Appends Japan to the England data but what I need is something like this:

 
FILEDEF PASS1 DISK PASS1.TXT
-RUN
TABLE FILE CAR
PRINT
     'CAR.ORIGIN.COUNTRY'
     'CAR.COMP.CAR'
     'CAR.CARREC.MODEL'
     'CAR.BODY.DEALER_COST'
     'CAR.BODY.RETAIL_COST'
WHERE CAR.ORIGIN.COUNTRY EQ 'ENGLAND';
ON TABLE HOLD AS PASS1 FORMAT FOCUS INDEX 'CAR.COMP.CAR'
END
FILEDEF PASS1 DISK PASS1.TXT (APPEND
-RUN
TABLE FILE CAR
PRINT
     'CAR.ORIGIN.COUNTRY'
     'CAR.COMP.CAR'
     'CAR.CARREC.MODEL'
     'CAR.BODY.DEALER_COST'
     'CAR.BODY.RETAIL_COST'
WHERE CAR.ORIGIN.COUNTRY EQ 'JAPAN';
ON TABLE HOLD AS PASS1 FORMAT FOCUS INDEX 'CAR.COMP.CAR'
END
TABLE FILE PASS1
PRINT *
END 
-EXIT 

This code only displays the Japan data. Is there a way to append to the Index File? The program I am working with actually goes to (2) two different files to retreive the data. I need to have the Indexed File to allow for Joins in my code due to Multiples in the From for the Join. Any Ideas??

Thanks,
Erin

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

If you want to APPEND..you should use format Alpha and .ftm

You could also do a MATCH or do some research on MORE

Prarie,
I decided to use the format focus index to allow for the flexibility I need for the final output of my report. When you say to use Format Alpha and .ftm do you mean instead of "ON TABLE HOLD AS PASS1 FORMAT FOCUS INDEX 'CAR.COMP.CAR'" to use something like "ON TABLE HOLD AS PASS1 FORMAT alpha INDEX 'Field_Name'" and change the PASS1.TXT to PASS1.ftm...?? I will look into using MATCH or MORE for the output.

Thanks,
Erin

Yes Format Alpha

and then Change Pass1.ftm

index wont work with alpha formats

I should use the more function but would do the index in a second stepp

If you are using FORMAT FOCUS, then you will need to use MODIFY.

FORMAT FOCUS is a database, adn you have to use tools to add to it, like an SQL DB.

TABLE FILE CAR
PRINT
     'CAR.ORIGIN.COUNTRY'
     'CAR.COMP.CAR'
     'CAR.CARREC.MODEL'
     'CAR.BODY.DEALER_COST'
     'CAR.BODY.RETAIL_COST'
WHERE CAR.ORIGIN.COUNTRY EQ 'ENGLAND';
ON TABLE HOLD AS PASS1 FORMAT FOCUS INDEX 'CAR.COMP.CAR'
END
-RUN
TABLE FILE CAR
PRINT
     'CAR.ORIGIN.COUNTRY'
     'CAR.COMP.CAR'
     'CAR.CARREC.MODEL'
     'CAR.BODY.DEALER_COST'
     'CAR.BODY.RETAIL_COST'
WHERE CAR.ORIGIN.COUNTRY EQ 'JAPAN';
ON TABLE HOLD AS PASS2
END
MODIFY FILE PASS1
FIXFORM FROM PASS2
DATA ON PASS2
END
TABLE FILE PASS1
PRINT *
END 

Once again it sounds like I have a couple of different ways I can go with this report. I think I will try to use Frank's suggestion and do a FORMAT FOCUS INDEX on the appended file in a separate step. For performance purposes I will also look into using the MORE command and the MODIFY command (per WAZ's suggestion) to see if I gain any performance by using these commands. I'm almost certain I will run into other issues as I work through this report. Thank you for the input and suggestions so far.

Erin

Erin,

You can not append to a FOCUS file using the FILEDEF ... (APPEND approach, you have to use either Maintain or MODIFY.

T

Tony,
can I do the following?

  
-* File IndexFiledef.fex
FILEDEF PASS1 DISK PASS1.TXT
-RUN
TABLE FILE CAR
PRINT
     'CAR.ORIGIN.COUNTRY'
     'CAR.COMP.CAR'
     'CAR.CARREC.MODEL'
     'CAR.BODY.DEALER_COST'
     'CAR.BODY.RETAIL_COST'
WHERE CAR.ORIGIN.COUNTRY EQ 'ENGLAND';
ON TABLE HOLD AS PASS1 
END
FILEDEF PASS1 DISK PASS1.TXT (APPEND
-RUN

TABLE FILE CAR
PRINT
     'CAR.ORIGIN.COUNTRY'
     'CAR.COMP.CAR'
     'CAR.CARREC.MODEL'
     'CAR.BODY.DEALER_COST'
     'CAR.BODY.RETAIL_COST'
WHERE CAR.ORIGIN.COUNTRY EQ 'JAPAN';
ON TABLE HOLD AS PASS1 
END
TABLE FILE PASS1
PRINT *
END 
-EXIT

Then add(+) the following code to Index the appended file (aka PASS1). Essentially just create the FORMAT FOCUS INDEX on the previous HOLD file something like this?

  
TABLE FILE PASS1
SUM
'CAR.BODY.DEALER_COST'
'CAR.BODY.RETAIL_COST'
BY 'CAR.ORIGIN.COUNTRY ' NOPRINT
ON TABLE HOLD AS PASS2 FORMAT FOCUS INDEX 'PASS1.PASS1.COUNTRY' 
END

Yes, although you may be more streamlined to use MORE as Frank suggested.

T

Tony,
I will try to add this to my report as described above. I will definitely try to implement Frank's suggestion and use the "MORE" function then do the FORMAT FOCUS INDEX in a separate step. For now I am going to see if what I have will work and the performance is 'ok'. If it is a slow process I will try to add the logic to use the MORE function to see if it helps with this performance.

Thanks,
Erin

You might be able to do something like this, using MORE:

SET HOLDLIST = PRINTONLY

TABLE FILE CAR
 PRINT
     'CAR.ORIGIN.COUNTRY'
     'CAR.COMP.CAR'
     'CAR.CARREC.MODEL'
     'CAR.BODY.DEALER_COST'
     'CAR.BODY.RETAIL_COST'
 WHERE CAR.ORIGIN.COUNTRY EQ 'ENGLAND';
 ON TABLE HOLD AS PASS1 FORMAT FOCUS INDEX 'CAR.ORIGIN.COUNTRY'

MORE
FILE CAR
 WHERE CAR.ORIGIN.COUNTRY EQ 'JAPAN';
END

Dan,
Can I use your logic IF the MORE FILE is different? something like 'BUS' instead of 'CAR':

TABLE FILE CAR
 PRINT
     'CAR.ORIGIN.COUNTRY'
     'CAR.COMP.CAR'
     'CAR.CARREC.MODEL'
     'CAR.BODY.DEALER_COST'
     'CAR.BODY.RETAIL_COST'
 WHERE CAR.ORIGIN.COUNTRY EQ 'ENGLAND';
 ON TABLE HOLD AS PASS1 FORMAT FOCUS INDEX 'CAR.ORIGIN.COUNTRY'

MORE
FILE BUS
 WHERE BUS.ORIGIN.COUNTRY EQ 'JAPAN';
END
  

Would that work as well....?

Thanks,
Erin

The only thing that must remain the same is the layout and format of columns - same number and order of columns and same field format. Doesn't matter where they come from or what the names are. The key is that the master file from the first (CAR) must be able to also read the data from the second.

Darin,
Thanks for the quick response. I will try to implement this into my code later this afternoon to see if it helps with my performance. The lay-out is identical, same number and order of columns as well as their formats. The only thing I am not certain of is the ability for the master files ability to read the data from the MORE file. I will let you know if I get any types of errors when I try to add this logic to the program.

Thanks,
Erin

To use MORE, only the field names and formats must match. The order of the columns and type of file/table are not important. And when appropriate, you can use DEFINEs and/or COMPUTEs to create the necessary field names and formats, if they don't already exist. In the example below, I created file CAR2 so it contains field names, formats, field order, and file type that are different from the CAR file. DEFINEs are used to create the required field names and formats for CAR2 to permit a MORE concatenation with the CAR file.

SET ASNAMES = ON
SET HOLDLIST = PRINTONLY

TABLE FILE CAR
 PRINT
  DEALER_COST/P7  AS 'FIELD1'
  MODEL/A34       AS 'FIELD2'
  CAR/A21         AS 'FIELD3'
  RETAIL_COST/F7  AS 'FIELD4'
  COUNTRY/A15     AS 'FIELD5'
  BODYTYPE/A19    AS 'FIELD6'
 ON TABLE HOLD AS CAR2
END

DEFINE FILE CAR2
 DEALER_COST/D7 = FIELD1 ;
 MODEL/A24      = EDIT(FIELD2,'999999999999999999999999$');
 CAR/A16        = EDIT(FIELD3,'9999999999999999$');
 RETAIL_COST/D7 = FIELD4 ;
 COUNTRY/A10    = EDIT(FIELD5,'9999999999$');
 BODYTYPE/A12   = EDIT(FIELD6,'999999999999$');
END

TABLE FILE CAR
 PRINT
  COUNTRY
  CAR
  MODEL
  BODYTYPE
  DEALER_COST
  RETAIL_COST
 WHERE COUNTRY EQ 'JAPAN';
 ON TABLE HOLD AS PASS1 FORMAT FOCUS INDEX COUNTRY
MORE
FILE CAR2
 WHERE COUNTRY EQ 'ENGLAND';
END

If you use PRINT when creating a FOCUS file, FOCUS will add a sequentially-numbered column named FOCLIST. You can avoid this by using the SUM verb with appropriate BY phrases.

Thanks for straightening that out Dan. I read throught the entire post and was still stuck on the APPENDing data sources and was probably thinking in flat file terms.

Anyway - to paraphrase the manual and make it as clear as possible:

The request must re-map the field names and formats second data source to match those used in the main request.
The main request names the first data source in the concatenation. It also defines the print and sort fields for both data sources.
The MORE phrase starts the subrequest that concatenates the second data source.
No display commands are allowed in the subrequest.
IF and WHERE criteria are the only report components permitted in a subrequest.


Field Name and Format Matching

All fields referenced in the main request must either exist with the same names and formats in all the concatenated files, or be re-mapped to those names and formats using virtual fields. Referenced fields include those used in COMPUTE commands, headings, aggregation phrases, sort phrases, and the PRINT, LIST, SUM, COUNT, WRITE, or ADD commands.

Dan,
I really got some good ideas from your post. Especially the SET ASNAMES = ON and the ability to manipulate the fields in the DEFINE FILE CAR2 by using the AS NAME is something I will definitely use in the future. I did not know that you could do that with the data.

I tried using the MORE function but didn't gain anything with the performance of the report. So I ended up just using the Filedef/append and creating a FORMAT FOCUS INDEX off of the hold file. It seems to be working fine and I have not noticed any issues with any of my JOINS due to the multiples in the FROM File.

Darin,
Thanks for clarifying/paraphrasing the manual so that I have a better understanding of what webfocus is actually doing or requires to work properly.

Thanks,
Erin

- Currently testing my program for glitches and issues if it doesn't fail I will mark this as resolved/solved