Hi all

I am a beginner to Focus and I have a doubt regarding cross referencing of segments.

I have two FOC DB s (say X and Y). Some fields in the first Foc DB s is being cross referenced to some fields in second table.

Let us assume that we have employee id in the first table (X). This column is cross-referenced with second table - Y to get the field employee name.

Focmast of Foc DB X:
SEGNAME=ABC,PARENT=X,SEGTYPE=KU,CRFILE=Y,CRKEY=EMPID

Focmast of Foc DB Y:
SEGNAME=ABC,SEGTYPE=S1,PARENT=ROOT,$
FIELDNAME=EMPID ,,A1 ,INDEX=I,$
FIELDNAME=EMPNAME ,,A15 ,$

I have a basic doubt in this. What happens if a employee id present in table X is not present in table Y? Will the empname be missing be stored as missing? Or fields in both tables would not be processed in the output?

Basically I would like to know what kind of join happens when there is a dynamic cross reference between two tables?

Any help would be appreciated. Thanks.

Hi DS and welcome to the world of FOCUS!

In your question you show that EMPID is linked as a KU segment. KU=Keyed Unique.

With Unique segments, when there isn't a match, FOCUS behaves as if the segment does exist and returns blank for alpha fields, zero for numerics.

By default, FOCUS joins resemble 1-1 left outer joins as you may know them from SQL.
You can turn those into 1-n left outer joins using the ALL or MULTIPLE keyword.

FOCUS doesn't do inner joins, but you can achieve the same result by adding a WHERE-clause to weed out the empty values resulting from the outer join.
There are INNER and LEFT_OUTER keywords, but those don't actually change the join between an inner or outer join.

Oh yes, you can't JOIN FOCUS databases on multiple columns either.

There are also conditional joins, with a different join syntax (boggles my mind why, but that's how it is).

IMNSHO, FOCUS databases have some catching up to do. As they are now, they're little more than flat files.
That's probably why they went for an entirely new database implementation (column-based) in WF8.

Welcome.

The FOCUS DB is different to a Relational DB, the two should not be compared directly; they work differently and fulfil different needs. FOCUS Database files are Hierarchical in nature, with relational attributes. This allows the building of cross referenced (JOINed) structures that may be quite complex in nature, though nothing like as bad as some relational structures can get! To describe them as flat files is disingenuous.

The FOCUS (and XFOCUS) files are extremely fast for loading and reporting and their use for Data Marts is very effective.

It is worth reading the chapter 'Logical Dependence: The Parent-Child Relationship' in the Describing Data manual. This may help explain the structure of FOCUS DBs for you. If you have time, check out the External Index, MDI-MultiDimensional Index (licence required) and the Intelligent Partitioning & LOCATION if using larger structures, all in the Describing Data Manual.

The MDI or GROUP field overcomes the commonly perceived, but incorrect, omission of multi field joins. MDI allowing multiple to multiple field joins, and GROUP giving multiple fields to a single GROUP join. The result is the same as multifield to multifield join.

JOINing FOCUS DBs can give all the basic JOIN types generally required, however it is also worth checking and understanding the SET ALL parameters which can help control the output from JOINed structures. Again some worthwhile reading is the chapter 'Handling Records With Missing Field Values' in the Creating Reports With WebFOCUS Language manual. This does explain all the different conditions that can apply when JOINing FOCUS DBs and what the difference between missing data and missing segments. It really helps but most people ignore it.

The Column Oriented database, that is a new adapter implementation, does NOT supercede the FOCUS DB, it is an additional product that may suit certain circumstances and certain customers.

quote:

If you have time, check out the External Index, MDI-MultiDimensional Index (licence required) and the Intelligent Partitioning & LOCATION if using larger structures, all in the Describing Data Manual.

I've seen MDI mentioned before and that it requires an extra licence. While we're at this, what arguments can we use to convince management that they need to buy that licence? Because that's the reason we don't have it...

Being able to do multi-field joins (didn't know MDI would enable that!) and multiple indexes on FOCUS DB's sounds good (although we don't have a lot of use for those, I think) from a technical point of view. Management however, won't know the difference as we're still able to provide them with the information they need in a reasonable amount of time.

Worse, we could write most of our current FOCUS databases into SQL databases and we wouldn't have these problems anymore.

It's a bit of a hard sell as it stands. IBI doesn't really help by providing very little information about their products, what those do or pricing of various components.

I must admit I am never sure on the official IB stance towards the FOCUS/XFOCUS db. It has many good features, is very fast, but the cost factor of the extra goodies is always going to go against it. Where it stands out is in the Data Mart area, but as many companies have already invested in other technologies, then the cost of this solution, rather than being an additional built-in feature, will always hamper its usage.

I understand the need for each product division to be a profit centre, but this approach does hamper ultimate utilisation, as the Sales teams are often unable/unwilling to provide convincing arguments for an extra cost item, though the end goal could be beneficial to the customer. Basically it is not a nice looking viewable object. It's a datafile!

Convincing someone of a need for a product is usually doomed to failure. Apart from the ease of maintenance when used in a Data mart type environment, the speed & flexibility, one of the main arguments for going into FOCUS DBs has been ownership. Whereas Relational DBs tend to be owned and administered by a company wide department, the FOCUS DB can have its ownership placed into the hands of separate departments; but would those departments pay for this privilege?

Thanks everyone for your responses. I was able to learn so much from each of your posts.

I also have one doubt in extension to this. What happens when a cross referenced DB is used inside a focexec which has SET ALL = ON option?

I learnt from your posts that cross referencing creates a full outer join. But I understand SET ALL = ON parameter forces a left join. How things work in this scenario?

Thanks again for all you posts.

I wish there was a simple answer. It depends upon the JOIN syntax used.

There are 2 types of JOIN syntax (more actually, but stick to 2 for now). The original JOIN code which was used prior to the introduction of Relational DB engines, and the JOIN code that 'mimics' the available JOINs available within SQL.

There is also the difference between MISSING segment instances and MISSING fields - fields with the MISSING=ON attribute in the master file.

When JOINing non-relational structures, e.g. FOCUS DB and flat files, I would stick to the original code, my preference only.
In the simplest form:

JOIN field in File1 to [ALL] field in File2

The SET ALL parameter only has any effect if the JOIN uses the ALL attribute, that is a one-to-many (KM) join; omit the ALL attribute and the structure is a one-to-one (KU) join.

In a KU join, the child segment to the parent is always deemed to exist, it behaves as if it were part of the parent segment. Here the segment is never MISSING, and if it does not exist, fields contained in that segment are blank or zero.

In a KM join, the child segment may not exist or may exist one or more times. If the child segment does not exist, the parent segment will not display on reports. This is the standard SET ALL=OFF. If you use SET ALL=ON (ON TABLE SET ALL ON), then parent segments without a child segment will display, and fields referenced from the child segment will display as MISSING (Display as missing, NOT treated as missing for screening).

Remember that this JOIN was pre relational DB, so whilst you can use terms like left outer, inner etc, this was not developed to comply with a concept that was 20 years away. So I believe that you can create an INNER join(ALL=OFF) and a Left Outer join(ALL=ON), but NOT a Full Outer Join.

Just to throw a spanner in the works, a Full Outer Join can be achieved using MATCH FILE...

Hey, that's a rather good explanation, you know that?

About implementing a Full Outer Join using MATCH FILE (with AFTER MATCH HOLD [AS file] OLD-OR-NEW, I suppose); how does that behave with one-to-many relations?

Okay, let us start lesson 2…
There was an old example on paper we used to use called Supplier Part. I've tried to recreate. Two FOCUS Files:

FILENAME=supplier, SUFFIX=FOC     , $
  SEGMENT=SUPPLIER, SEGTYPE=S1, $
    FIELDNAME=Supplier, ALIAS=Supplier, USAGE=A4,
      TITLE='Wholesaler', $
  SEGMENT=PART, SEGTYPE=S1, PARENT=SUPPLIER, $
    FIELDNAME=Part, ALIAS=Part, USAGE=A4,
      TITLE='Stock Item', $

FILENAME=part, SUFFIX=FOC     , $
  SEGMENT=PART, SEGTYPE=S1, $
    FIELDNAME=Part, ALIAS=PART, USAGE=A4, ACTUAL=A20,
      TITLE='Garden Tool', $
    FIELDNAME=Description, ALIAS=DESC, USAGE=A20,
      TITLE='Tool Description', $

Data looking like this for supplier:

Wholesaler	 Stock Item
1	         A
1	         B
1	         C
1	         E
2	         B
2	         D
2	         J
3	         A
3	         B
3	         C
3	         D
3	         E
3	         F
3	         G
3	         H
3	         J
4	         G
4	         H
5	         A
5	         B
5	         D
6	         .
7	         E
7	         J

and this for part:

Garden Tool	 Tool Description
A	         Spade
B	         Fork
C	         Hoe
D	         Rake
E	         Shovel
F	         Trowel
G	         Shears
H	         Secateurs
I	         Dibber
J	         Pick Axe

So for a full outer, using MATCH FILE:

SET ALL=ON
MATCH FILE Part
SUM Description
BY Part
RUN
FILE Supplier
PRINT Supplier
BY Part
AFTER MATCH HOLD AS ALL_ITEMS OLD-OR-NEW
END
-RUN
TABLE FILE ALL_ITEMS
PRINT Part Description
BY Supplier
ON TABLE SET BYDISPLAY ON
ON TABLE SET PAGE NOPAGE
END
-RUN 
TABLE FILE ALL_ITEMS
PRINT Description Supplier
BY Part
ON TABLE SET BYDISPLAY ON
ON TABLE SET PAGE NOPAGE
END
-RUN 

gives:

Supplier	 Part	 Description
 	         I	 Dibber
1	         A	 Spade
1	         B	 Fork
1	         C	 Hoe
1	         E	 Shovel
2	         B	 Fork
2	         D	 Rake
2	         J	 Pick Axe
3	         A	 Spade
3	         B	 Fork
3	         C	 Hoe
3	         D	 Rake
3	         E	 Shovel
3	         F	 Trowel
3	         G	 Shears
3	         H	 Secateurs
3	         J	 Pick Axe
4	         G	 Shears
4	         H	 Secateurs
5	         A	 Spade
5	         B	 Fork
5	         D	 Rake
6	  	  
7	         E	 Shovel
7	         J	 Pick Axe



Part	 Description	 Supplier
 	  	         6
A	 Spade	         1
A	 Spade	         3
A	 Spade	         5
B	 Fork	         1
B	 Fork	         2
B	 Fork	         3
B	 Fork	         5
C	 Hoe	         1
C	 Hoe	         3
D	 Rake	         2
D	 Rake	         3
D	 Rake	         5
E	 Shovel	         1
E	 Shovel	         3
E	 Shovel	         7
F	 Trowel	         3
G	 Shears	         3
G	 Shears	         4
H	 Secateurs	 3
H	 Secateurs	 4
I	 Dibber	  
J	 Pick Axe	 2
J	 Pick Axe	 3
J	 Pick Axe	 7

So easy to see that Supplier 6 supplies no parts and the part I (Dibber) is not supplied by anyone.
However, there are many more things that can be done.

Q1. Which supplier supplies no parts?

SET ALL=PASS
TABLE FILE supplier
PRINT Supplier
IF Part EQ 'X'
END

ALL=PASS says that any MISSING SEGMENTS will pass screening conditions applied to a field in that segment.
Q2. Which suppliers supply both a Spade and a Fork?

TABLE FILE SUPPLIER
PRINT Supplier
IF Part INCLUDES A AND B
END

The INCLUDES and EXCLUDES allow the multiple selection of items that must exist (or not) in the child segment.
There were a number of questions based around this, all answerable in one pass of the data.