I am extracting the FOCUS database file into a flat file. The database file contain the multiple segments.I am trying to extract 2000 records from the file by giving the command WHERE RECORDLIMIT=2000, but the reords extracted to the flat file is less than 2000.I also included SET ALL = ON command in the program. Can any one help me, how to get the 2000 records in to the O/P file.
Thanks, Rajesh
March 09, 2007, 02:16 PM
Alan B
Rajesh
The recordlimit refers to the number of records retrieved from the file. If you are doing a SUM rather than a PRINT, you may easily end up with fewer output records as a result.
So you amy want to take this approach:
DEFINE FILE CAR
CNTR/I4 WITH CAR = CNTR+1;
END
TABLE FILE CAR
SUM SALES
BY CAR
IF CNTR LE 5
END
Basically the CNTR field needs to be WITH the lowest sort level. You will have to customise a litle for your file/data, but the principle should work.
Alan. WF 7.705/8.007
March 09, 2007, 03:14 PM
ET
I do not know what Focus counts as a record when a heirachical multipathed Focus database is used. See my examples below using the car file with recordlimit of 5.
TABLE FILE CAR PRINT COUNTRY IF RECORDLIMIT EQ 5 END
COUNTRY ------- ENGLAND JAPAN ITALY W GERMANY FRANCE
This returns 5 records. When I add data from additional multipathed segments keeping the same recordlimit I now only get 3 records out.
TABLE FILE CAR PRINT COUNTRY MODEL STANDARD IF RECORDLIMIT EQ 5 END
COUNTRY MODEL STANDARD ------- ----- -------- ENGLAND V12XKE AUTO POWER STEERING ENGLAND XJ12L AUTO RECLINING BUCKET SEATS ENGLAND . WHITEWALL RADIAL PLY TIRES
To still get 5 records out I arbitrarily bumped up the recordlimit to some number larger than 5 but short of dumping the whole database (experiment to get this number in your case - bump up your recordlimit until you get a liitle over 2000 records out). Then compute a counter and do an if total test on the computed counter for number to want to output (5 for me - 2000 for you) like this .
TABLE FILE CAR PRINT COUNTRY MODEL STANDARD COMPUTE CNTR/I1=CNTR + 1; NOPRINT IF RECORDLIMIT EQ 20 IF TOTAL CNTR LE 5 END
COUNTRY MODEL STANDARD ------- ----- -------- ENGLAND V12XKE AUTO POWER STEERING ENGLAND XJ12L AUTO RECLINING BUCKET SEATS ENGLAND . WHITEWALL RADIAL PLY TIRES ENGLAND . WRAP AROUND BUMPERS ENGLAND . 4 WHEEL DISC BRAKES
Good luck
et
FOCUS 7.6 MVS PDF,HTML,EXCEL
March 09, 2007, 03:26 PM
Alan B
If you are extracting over multi paths, then the approach in my previous response will still work.
In the case of the previous response, use 2 Counter fields, each associated with the lowest level of the individual path. In this case, reporting across MODEL and STANDARD, use one WITH MODEL and one WITH STANDARD, then use a WHERE CNTR1 LE 5 AND CNTR2 LE 5.
Alan. WF 7.705/8.007
March 09, 2007, 04:47 PM
Rajesh Reddy
Hi,
Thanks for you replay. I worked on with your code, It will pulled the 2000 records to O/P file, but it will reading the more number of records. I am looking for the number of records it will read and the same number of records in the O/P file. Can you help me out on this.
Thanks, Rajesh.
March 09, 2007, 05:17 PM
Alan B
Rajesh
I think I need more information from you to help here. The master file you are extracting from, or the relevent pieces, and the TABLE request you are using. Thanks,
So in terms of a flat type file you would have 3 different record type, and as such you should do 3 extracts, one for each path, to obtain an accurate record and relationship.
Otherwise what you are attempting to get is a non-existent relationship between EMPTBRCH, MJUNIT and MNUNIT. In FOCUS terms, when it is retrieving a record across multiple paths, then the differences in the numbers of instances of each of the segments in each path will cause the apparent anomoly you are seeing; unless there are equal numbers of segment instances across all paths, which is highly unlikely.
Alan. WF 7.705/8.007
March 10, 2007, 12:39 PM
Danny-SRL
Rajesh, If you are trying to output all data from the focus file then I suggest the following TABLE FILE EMPIDATA PRINT SEG.EMPIENTNBR SEG.EMPNAME SEG.EMPNAM1 EMPBRANCH MJUNIT MJAPRJ MDUNIT MDAPRJ MNUNIT MNAPRJ END
You will get all the occurrences of every segment. It is highly likely the the number of output LINES is less than the number of records because probably you do not have occurrences for all child segments. You'll have to decide what you call an output record.
Try the car file for example: TABLE FILE CAR PRINT COUNTRY CAR MODEL SEG.BODY SEG.MPG SEG.STANDARD SEG.WARRANTY ON TABLE HOLD END
You will see: NUMBER OF RECORDS IN TABLE= 42 LINES= 25
Good luck!
Daniel In Focus since 1982 wf 8.202M/Win10/IIS/SSA - WrapApp Front End for WF
March 10, 2007, 01:46 PM
Alan B
You have to be careful and/or brave when traversing multi-path/multi-record data files and placing the results into a single flat file. The FOCUS file structure, amongst others, allows not only for multiple levels of data, but also for multiple record types in a single structure, which can require a different approach to standard, normal formed, SQL tables.
The basic concern is one of relationship between records. The only actual relationship between data in multiple paths is at the level of the parent of all of the paths. In the CAR example that Danny showed, you will get records out that gives the standard equipment for a model that can easily be interpreted incorrectly, like this:
From this information you might think that the XJ12L does not have 4 wheel disc brakes, whereas it does, as the equipment is related to the CAR, not the MODEL. So processing this output could be, at best, misleading. This approach shows a relationship between model and equipment, which actually only exists through the parent, CAR. So to overcome this, you have to use the Cartesian product, CARTESIAN=ON, to associate every model with every piece of equipment. Then you have a correct association, and instead of the 25 records, you will get, I think, 34 (though the number of records still shows 25), but BMW is missing from the output. The Cartesian product has linked each body to every piece of standard equipment, but BMW has no standard equipment (some things never change!) and cannot, therefore, be part of the Cartesian product between the 2 elements.
So going back to what I was originally saying; trying to create a single flat file from multiple paths is acceptable only if you are prepared to have incorrect data associations (CARTESIAN=OFF, the default), or missing data (CARTESIAN=ON), or know absolutely that every piece of data inter-relates between the paths in a manner that will show up correctly associated. Otherwise use one extract for each path, and then you will get the correct number of LINES processed to the number of RECORDS output.
Hopefully Rajesh will look at extracting to 3 files, one for each of the paths in the data file.
Alan. WF 7.705/8.007
March 10, 2007, 05:58 PM
FrankDutch
clear and correct explanaition Alan. should be put in the manual.
Frank
prod: WF 7.6.10 platform Windows, databases: msSQL2000, msSQL2005, RMS, Oracle, Sybase,IE7 test: WF 7.6.10 on the same platform and databases,IE7
March 12, 2007, 04:27 PM
Rajesh Reddy
Hi Alan/Frank,
Thanks a lot for your valuble inputs, I will work on your solutions.
Thanks, RajeshThis message has been edited. Last edited by: Rajesh Reddy,