Hi All,

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

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.

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

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.

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.

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,

Hi Alan,

Please find the master file structure.

FILENAME = EMPIDATA, SUFFIX = FOC,$

SEGNAME = EMPIENT , SEGTYPE = S1 ,$
FIELDNAME = EMPIENTNBR , ALIAS = EMPNO , A10 ,FIELDTYPE = I,$
FIELDNAME = EMPSHORTNAME , ALIAS = EMPSH , A25 ,$ (15)

SEGNAME = EMPNTUNQ , SEGTYPE = U, PARENT = EMPIENT ,$

FIELDNAME = EMPNAME , ALIAS = EMPNM , A60 ,$
FIELDNAME = EMPSTATUS , ALIAS = EMPS , A1 ,$
FIELDNAME = EMPTERMDT , ALIAS = EMPTD , I6YMD,$
DEFINE EMPTERMSD/YMD = EMPTERMDT;

SEGNAME = EMPNTUNQ2, SEGTYPE = U, PARENT = EMPIENT ,$

FIELDNAME = EMPNAM1 , ALIAS = EMPN1 , A9 ,$
FIELDNAME = EMPNAM2 , ALIAS = EMPN2 , A9 ,$

SEGNAME = EMPNTBRCH, SEGTYPE = S1, PARENT = EMPIENT, $
FIELDNAME = EMPBRANCH , ALIAS = EMPBR , I6 ,$

SEGNAME = MAJDEUNT , SEGTYPE = S1 , PARENT = EMPIENT ,$

FIELDNAME = MJUNIT ,, A8 ,$
SEGNAME = MAJDE2 , SEGTYPE =S3 , PARENT = MAJDEUNT,$
FIELDNAME = MJAPRJ ,, A3 ,$
SEGNAME = MIDDEUNT , SEGTYPE = S1 , PARENT = MAJDEUNT ,$

FIELDNAME = MDUNIT ,, A8 ,$
SEGNAME = MIDDE2 , SEGTYPE = S3 , PARENT = MIDDEUNT,$
FIELDNAME = MDAPRJ ,, A3 ,$

SEGNAME = MINDEUNT , SEGTYPE = S1 , PARENT = MIDDEUNT ,$

FIELDNAME = MNUNIT ,, A8 ,$
SEGNAME = MINDE2 , SEGTYPE = S3 , PARENT = MINDEUNT, $
FIELDNAME = MNAPRJ ,, A3 ,$

We are trying to extract 2000 records, we are getting 1773 reords in O/P file.

LOG file it showing :

NUMBER OF RECORDS IN TABLE= 2000 LINES= 1773

Thanks,
Rajesh

Rajesh

As you can see this master descripes a multyple path database.
that will be the reason for your result.
try in your fex to list parts of these path.

Rajesh

Frank is correct, it will be the multi-paths that are causing your issue.

basically in your file you have 3 paths:

EMPIENT - EMPTBRCH
EMPIENT - MAJDEUNT - MJUNIT
EMPEINT - MIDDEUNT - MINDEUNT - MNUNIT

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.

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!

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:

JAGUAR - V12XKE - 4 wheel disc brakes
JAGUAR - XJ12lL - power steering
JAGUAR - . - reclining bucket seats
etc.

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.

clear and correct explanaition Alan.
should be put in the manual.

Hi Alan/Frank,

Thanks a lot for your valuble inputs, I will work on your solutions.

Thanks,
Rajesh

This message has been edited. Last edited by: Rajesh Reddy,