Focus multi segment files

the difference between a flat file and a segmented one is pretty much the difference between an 8-track and an iPod.
Look at the car file
TABLE FILE CAR PRINT COUNTRY
END
..how many lines do you get?
..do you get as many lines as there are countries?
..or do you get as many lines as there are granular elements?
See?
... if not, repeat
TABLE FILE CAR PRINT COUNTRY CAR
END
... if you still dont see it,
TABLE FILE CAR PRINT COUNTRY CAR MODEL
END

In Focus since 1979///7706m/5 ;wintel 2008/64;OAM security; Oracle db, ///MRE/BID

Posts: 3811 | Location: Manhattan | Registered: October 28, 2003

Ignored post by susannah posted

GinnyJakes

Expert

posted

A couple of other considerations.

How big is this file going to be? A multi-segment file makes more efficient use of space as you don't have to repeat keys and data on every row.

It can also be more efficient on the data retrieval side especially if you have screening conditions in your report that screen at the highest levels possible. That way you don't have to read segments you don't need if the top segs don't meet the selection criteria.

If the file is neither big nor wide, then I wouldn't worry about it.

Ginny
---------------------------------
Prod: WF 7.7.01 Dev: WF 7.6.9-11
Admin, MRE,self-service; adapters: Teradata, DB2, Oracle, SQL Server, Essbase, ESRI, FlexEnable, Google

Posts: 2723 | Location: Ann Arbor, MI | Registered: April 05, 2006

Ignored post by GinnyJakes posted

FrankDutch

Virtuoso

posted

How do you plan to convert the flat file to a multisegment focus file.
You know you might be able to create the multisegment file direct.

	*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, 2006

Ignored post by FrankDutch posted

Guru

posted

quote:
if you have screening conditions in your report that screen at the highest levels

I have a situation that screening must be done at the lower level and got an error if mult-path

TABLE FILE CAR PRINT COUNTRY CAR MODEL SEATS WARRANTY
WHERE WARRANTY EQ '6 MONTHS OR 6000 MILES' OR SEATS EQ 4
END

(FOC246) COMPUTATIONAL STATEMENT REFERS TO MORE THAN ONE DATA PATH

So I remove the screening, and put the result in the hold file then apply the screening on the hold file. My hold file will be huge without screening. Does this hurt the performance? Should I screen individual files then join them together?

Hua

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

Developer Studio 7.6.11
AS400 - V5R4
HTML,PDF,XLS

Posts: 305 | Location: Winnipeg,MB | Registered: May 12, 2008

Ignored post by Hua posted

j.gross

Virtuoso

posted

The problem is that both Seats and Warranty have a one-to-many relationship with Car [the way CAR is loaded there is at most one WARANT segment instance per COMP (company) instance, so one would expect it to be a U segment, but it's declared as S1].

You can use an alternate view to avoid the problem.

TABLE FILE CAR.WARRANTY
PRINT WARRANTY SEATS
BY COUNTRY BY CAR BY MODEL
WHERE WARRANTY EQ '6 MONTHS OR 6000 MILES' OR SEATS EQ 4;
END

...works, returning 4 rows.

CHECK FILE CAR.WARRANTY PICT
 STRUCTURE OF FOCUS    FILE CAR      ON 02/03/09 AT 14.55.21
 VIEWED FROM SEGMENT WARANT
 WARANT
 01      S1
 **************
 *WARRANTY    **  <=====
 *            **
 *            **
 *            **
 *            **
 ***************
 **************
 I
 I
 I
 I COMP
 02    I KLU      <===== note the physical parent becomes a logical Unique child in the inverted view
 **************
 *CAR         *
 *            *
 *            *
 *            *
 *            *
 **************
 I
 I
 +-----------------+-----------------+
 I                 I                 I
 I ORIGIN          I CARREC          I EQUIP
 03    I KLU       04    I KL        07    I KL
 **************    **************    **************
 *COUNTRY     *I   *MODEL       **   *STANDARD    **
 *            *    *            **   *            **
 *            *    *            **   *            **
 *            *    *            **   *            **
 *            *    *            **   *            **
 **************    ***************   ***************
 **************    **************
 I
 I
 I
 I BODY
 05    I KL
 **************
 *BODYTYPE    **
 *SEATS       **
 *DEALER_COST **
 *RETAIL_COST **
 *            **
 ***************
 **************
 I
 I
 I
 I SPECS
 06    I KLU
 **************
 *LENGTH      *
 *WIDTH       *
 *HEIGHT      *
 *WEIGHT      *
 *            *
 **************

If, in real life, your Where condition involved more than two independent paths, you'd still be left with two.

You can use SET CARTESIAN=ON to avoid the error message -- but be prepared to deal with the potentially explosive results.

Posts: 1925 | Location: NYC | In FOCUS since 1983 | Registered: January 11, 2005

Ignored post by j.gross posted

GinnyJakes

Expert

posted

This is what the error message means:

(FOC246) COMPUTATIONAL STATEMENT REFERS TO MORE THAN ONE DATA PATH
All data fields referenced by a computational expression must occur
in a single top-to-bottom data path.

You could try extracting from one path with its screening condition, holding, then extracting from the other with its screening condition, holding, then join.

You probably should have started your own thread for this question.

Ginny
---------------------------------
Prod: WF 7.7.01 Dev: WF 7.6.9-11
Admin, MRE,self-service; adapters: Teradata, DB2, Oracle, SQL Server, Essbase, ESRI, FlexEnable, Google

Posts: 2723 | Location: Ann Arbor, MI | Registered: April 05, 2006

Ignored post by GinnyJakes posted

Guru

posted

Although this is not my thread, I want to thank you all for openning up a new world for me. I know mult-segment structure is neat, but didn't have the full knowledge to take advantage of it. I never knew I can stretch the U to have a straight line like this. Thanks j.gross!

Hua

Developer Studio 7.6.11
AS400 - V5R4
HTML,PDF,XLS

Posts: 305 | Location: Winnipeg,MB | Registered: May 12, 2008

Ignored post by Hua posted

Tony A

Expert

posted

Obviously Jack's suggestion is the one to go with (it's what I would have done), but .....

To add to Ginny's suggestion, beware of multi data handling. In the method that Ginny gives you are handling the entire database twice and then joining (database x 2, matrix x 3, hold files x 2 and 1 result file).

If you were to use the screening conditions within MATCH logic you would still handle the entire database twice but in your first hold file you would have the results of your JOIN without having to handle the subsequent data again (database x 2, matrix x 2 and 1 result file).

Simple rule - design your extract, don't just code it and hope for the best. If efficiency is your most important consideration then a good extract design is paramount.

T

In FOCUS since 1986	WebFOCUS Server 8.2.01M, thru 8.2.07 on Windows Svr 2008 R2
	WebFOCUS App Studio 8.2.06 standalone on Windows 10

Posts: 5694 | Location: United Kingdom | Registered: April 08, 2004

Ignored post by Tony A posted

Guru

posted

extiger, I apologize for stealing the show here. Since Tony brought this up again, I really like to elaborate my case a litte bit... hope I can get some good advices here.

I am totally agree with Tony the importance of good design, and I am not saying I will go for Jack's way(that just a new discovery for me).

Anyway, my goal is to show the payable invoices along with (optionally) the cheque#, PO# and/or receiver#, allowing the users to select by vendor#, invoice#, cheque#, po# receiver#, invoice date.
My challenges are:
- at least 4 large transaciton files, with invoice is the biggest one
- each selection is optional, ie vendor = &vendor# or &vendor = "all" ... etc, if none selected, I'll show 100 most recent invoices. so where is the begining of my linear search path?
- the timing of all the activities is the issue here: only PO is known to exist, whether the invoice created for the PO, goods received or cheque is cut for the invoice are all questionable( guess my job is to answer these questions, eh? ). The types of joins are very much depend on the selections

The easiest way to line them all up first, then pick the ones I like. But guarrantee some one will scream. I've been pondering for sometime now how to approach this...

Hua

Developer Studio 7.6.11
AS400 - V5R4
HTML,PDF,XLS

Posts: 305 | Location: Winnipeg,MB | Registered: May 12, 2008

Ignored post by Hua posted

susannah

Expert

posted

Then write all possible JOIN's in your fex...
preceeded by some code that determines all the user's selections, and then turn the JOINs ONor OFF depending on the selection.

-SET &cmtj1 = IF &MYVALUE IS 'RICKY' THEN '' ELSE '-*';
...
&cmtj1.EVAL JOIN ACTOR IN HOST TO ACTOR IN GUEST AS J1
...
so this JOIN will be in effect only when your parms have determined

In Focus since 1979///7706m/5 ;wintel 2008/64;OAM security; Oracle db, ///MRE/BID

Posts: 3811 | Location: Manhattan | Registered: October 28, 2003

Ignored post by susannah posted

Guru

posted

Thanks Susanna.

I probably take your approach...
Select invoices,
join the cheques if cheque# is entered
join the POs if PO# is entered
join the Recievers if Rev# is entered

I should have a fewer invoices to do the left-outter joins again with cheques/POs/receiver files.

Hua

Developer Studio 7.6.11
AS400 - V5R4
HTML,PDF,XLS

Posts: 305 | Location: Winnipeg,MB | Registered: May 12, 2008

Ignored post by Hua posted

Piipster

Guru

posted

quote:
(FOC246) COMPUTATIONAL STATEMENT REFERS TO MORE THAN ONE DATA PATH

When you get this error message you can also apply your selections using a WHERE TOTAL. This gets applied on the internal matrix which in this example now only has one segment.

ttfn, kp

Access to most releases from R52x, on multiple platforms.

Posts: 346 | Location: Melbourne Australia | Registered: April 15, 2003

Ignored post by Piipster posted

Guru

posted

piister, I tried with WHERE TOTAL SEATS after summing on the SEATS, it didn't give me the error, neither when I break down the Where clause into 2 separate WHERE clauses, except it gives me the AND result rather than OR.

I am having a 2nd thought about my approach, I want to do the conditional comments on the WHERE clauses, that is if someone kindly validate my assumptions...

- Is it true that the joins defined at master file level are the same as the joins defined inside the fex, and are purely definitions only (physical links not yet happenned)?
- From Susanna's very first replay, her illustrations showed that the physical joins are depending on what is selected to PRINT/WHERE. Is it true there won't be any join for TABLE FILE CAR PRINT CAR END?

Thanks,

Hua

Developer Studio 7.6.11
AS400 - V5R4
HTML,PDF,XLS

Posts: 305 | Location: Winnipeg,MB | Registered: May 12, 2008

Ignored post by Hua posted

Guru

posted

quote:
Originally posted by Hua:
piister, I tried with WHERE TOTAL SEATS after summing on the SEATS, it didn't give me the error, neither when I break down the Where clause into 2 separate WHERE clauses, except it gives me the AND result rather than OR.

I am having a 2nd thought about my approach, I want to do the conditional comments on the WHERE clauses, that is if someone kindly validate my assumptions...

- Is it true that the joins defined at master file level are the same as the joins defined inside the fex, and are purely definitions only (physical links not yet happenned)?
- From Susanna's very first replay, her illustrations showed that the physical joins are depending on what is selected to PRINT/WHERE. Is it true there won't be any join for TABLE FILE CAR PRINT CAR END despite CAR is multi-segmented?

Thanks,

Hua

Developer Studio 7.6.11
AS400 - V5R4
HTML,PDF,XLS

Posts: 305 | Location: Winnipeg,MB | Registered: May 12, 2008

Ignored post by Hua posted