| Title: | FOCUS, from INFORMATION BUILDERS |
| Moderator: | ZAYIUS::BROUILLETTE |
| Created: | Thu Feb 19 1987 |
| Last Modified: | Mon May 05 1997 |
| Last Successful Update: | Fri Jun 06 1997 |
| Number of topics: | 615 |
| Total number of notes: | 1779 |
I have the following relations in a large Order history RdB; Header, Detail and Part. There is a one-to-many relationship from Header to Detail and there is a many-to-one from Detail to Part relation. The .acx file had Header as the root, header as the parent for detail and detail as the parent for part. This worked fine. But, I wanted to force FOCUS to access the Detail relation first in order to carry out a RSE on the DAT_INVOICE field. This was for performance reasons. The report was as follows Print Some Header info and some Detail info If DAT_INVOICE (a field in the Detail) GT 890702 To force Focus to access the Detail first, I made the Detail the root in the .acx file, Detail then beame the parent for Header. This also worked fine, and Focus used the DAT_INVOICE index. The performance increased from 42 mins CPU time to 7 mins. However, another FEX returned the following error message (FOC029) SORT KEYS NOT IN SINGLE TOP-TO-BOTTOM SEGMENT PATH Have I done something wrong in making Detail the root. Now the root has a many-to-one with the detail and the detail continues to have a many- to-one with the Part relation. Any info on this would be appreciated. Regards, Dave
| T.R | Title | User | Personal Name | Date | Lines |
|---|---|---|---|---|---|
| 301.1 | Some success sofar | ILO::BROCKLEBANK | Fri Jan 12 1990 08:46 | 9 | |
One sucess sofar was to revert the .acx & .mas files to the
original format with Order-Header as the root. Then using
TABLE FILE SWFMIC.DAT_INVOICE
to force Focus to access the Detail relation first for all
reports not using the sort sequence causing the problem.
Any info on why th sort error message is given would be appreciated.
Dave
| |||||
| 301.2 | Little Boxes | SHAPES::CARSEM | Mon Jan 15 1990 05:41 | 93 | |
I don't know if you're aware of the Focus 'CHECK FILE PICTURE' command
(...see Focus Users Manual Chapter 12...) but I think it may help you
understand your problem.
To begin with, Focus is a _HIERARCHICAL_ database system and NOT a
RELATIONAL database system. When you create a Focus description of
two or more relations of a RDB database, Focus views the relations
as a pseudo-hierarchical structure depending on how they are described
in the .MAS and .ACX files.
Therefore, in your original example, Focus will see the relations as
the following structure:
HEADER
01 S0
**************
*FIELD1 **
*FIELD2 **
*FIELD3 **
*FIELD4 **
* **
**************
I
I
I DETAILS
02 I S0
**************
*FIELD1 **
*FIELD2 **
*FIELD3 **
*FIELD4 **
* **
**************
I
I
I PARTS
03 I S0
**************
*FIELD1 **
*FIELD2 **
*FIELD3 **
*FIELD4 **
* **
**************
This is a straight line hierarchy and all the fields would be available
for any request.
However in your second scenario, I think you have linked the relations
into the following structure:
DETAILS
01 S0
**************
*FIELD1 **
*FIELD2 **
*FIELD3 **
*FIELD4 **
* **
**************
I
I
I--------------------I
I I
I HEADER I PARTS
02 I S0 03 I S0
************** **************
*FIELD1 ** *FIELD1 **
*FIELD2 ** *FIELD2 **
*FIELD3 ** *FIELD3 **
*FIELD4 ** *FIELD4 **
* ** * **
************** **************
Focus can only access data using one 'path' through the database
structure at one time.
In the above example, when retrieving data in a 'TABLE' request,
Focus will start access through the DETAILS relation and then can
proceed down the structure to _EITHER_ the HEADER relation _OR_ the
PARTS relation...BUT NOT BOTH SIMULTANEOUSLY!!
If your request contains fields from both the HEADER and PARTS
relations, then Focus will generate the error message you have been
seeing.
If the second structure is the one you want, there is a way to get
data from both paths out in a 'single' request using the 'MATCH'
command. But that's another story...
David Carse - European ADG, Basingstoke
| |||||
| 301.3 | One solution? | ILO::BROCKLEBANK | Tue Jan 16 1990 09:10 | 31 | |
From: PICA::COYLE 12-JAN-1990 19:14:16.02
To: ILO::BROCKLEBANK
CC:
Subj: Have you tried this?
Hi Dave,
Don't know if you've had any more luck, but have you tried the following:
SEGNAME=ORD_DET,SEGTYPE=S0
...
SEGNAME=PARTS,SEGTYPE=S0,PARENT=ORD_DET
...
SEGNAME=ORD_HEAD,SEGTYPE=U,PARENT=ORD_DET
...
In this case you are making ORD_HEAD a unique segment.
Let me know how you make out.
Thanks,
KC
| |||||
| 301.4 | Unique segment the winner | ILO::BROCKLEBANK | Tue Jan 16 1990 09:17 | 19 | |
Re Note .3
David I don't understand why FOCUS can't retrieve data from 2
child relations linked to the same parent. Any further info
would be useful.
Re Note .4
Kevin's solution to make the Header child relation a Unique
segment worked. The good thing about this solution is that
now all reports which use fields in the Detail relation
access this relation first. Thus using the RdB index on the
date field, the most efficient search strategy is used.
Because our RdB is so big (2 years history of orders), this
search strategy of using an RdB index on DATE_INVOICED has
increased the performance by 500%.
Thanks to the replies on this problem.
Regards,
Dave
| |||||