Focal Point Banner


As of December 1, 2020, Focal Point is retired and repurposed as a reference repository. We value the wealth of knowledge that's been shared here over the years. You'll continue to have access to this treasure trove of knowledge, for search purposes only.

Join the TIBCO Community
TIBCO Community is a collaborative space for users to share knowledge and support one another in making the best use of TIBCO products and services. There are several TIBCO WebFOCUS resources in the community.

  • From the Home page, select Predict: WebFOCUS to view articles, questions, and trending articles.
  • Select Products from the top navigation bar, scroll, and then select the TIBCO WebFOCUS product page to view product overview, articles, and discussions.
  • Request access to the private WebFOCUS User Group (login required) to network with fellow members.

Former myibi community members should have received an email on 8/3/22 to activate their user accounts to join the community. Check your Spam folder for the email. Please get in touch with us at community@tibco.com for further assistance. Reference the community FAQ to learn more about the community.


Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     Regd: Focus database extraction

Read-Only Read-Only Topic
Go
Search
Notify
Tools
Regd: Focus database extraction
 Login/Join
 
Member
posted
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
 
Posts: 14 | Registered: January 19, 2006Report This Post
Virtuoso
posted Hide Post
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
 
Posts: 1451 | Location: Portugal | Registered: February 07, 2007Report This Post
Platinum Member
posted Hide Post
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
 
Posts: 115 | Location: Chicago, IL | Registered: May 28, 2004Report This Post
Virtuoso
posted Hide Post
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
 
Posts: 1451 | Location: Portugal | Registered: February 07, 2007Report This Post
Member
posted Hide Post
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.
 
Posts: 14 | Registered: January 19, 2006Report This Post
Virtuoso
posted Hide Post
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,


Alan.
WF 7.705/8.007
 
Posts: 1451 | Location: Portugal | Registered: February 07, 2007Report This Post
Member
posted Hide Post
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
 
Posts: 14 | Registered: January 19, 2006Report This Post
Virtuoso
posted Hide Post
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.




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

 
Posts: 2387 | Location: Amsterdam, the Netherlands | Registered: December 03, 2006Report This Post
Virtuoso
posted Hide Post
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.


Alan.
WF 7.705/8.007
 
Posts: 1451 | Location: Portugal | Registered: February 07, 2007Report This Post
Virtuoso
posted Hide Post
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

 
Posts: 1980 | Location: Tel Aviv, Israel | Registered: March 23, 2006Report This Post
Virtuoso
posted Hide Post
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.


Alan.
WF 7.705/8.007
 
Posts: 1451 | Location: Portugal | Registered: February 07, 2007Report This Post
Virtuoso
posted Hide Post
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

 
Posts: 2387 | Location: Amsterdam, the Netherlands | Registered: December 03, 2006Report This Post
Member
posted Hide Post
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,
 
Posts: 14 | Registered: January 19, 2006Report This Post
  Powered by Social Strata  

Read-Only Read-Only Topic

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     Regd: Focus database extraction

Copyright © 1996-2020 Information Builders