I have two hold files. I have confirmed the contents. Security is an issue or I’d post all code. Here is the structure and contents of each Hold file – loaded with only sample data thus far for sake of example:

H1:
StudentId.....CollegeApplied
1111..........PennState
1111..........NYU

H2:
StudentId.....SATscoreAttained…….SATdate
1111..........1170………………………..3/4/2013
1111..........1030…………………………2/28/2012

I want to join the two hold files and expect this result:

StudentId.....College.....SATscore……….SATdate
1111..........PennState...1170………….……3/4/2013
1111..........PennState...1030……………….2/28/2012
1111..........NYU.........1170…………………3/4/2013
1111..........NYU.........1030………..……….2/28/2012

*make believe there was a mailing or something to each college collecting SAT scores......the issue is that after I join H1 to H2 on StudentId as a Single Instance (or multiple) Left Outer Join and run a report, the result I instead get is:

StudentId.....College.....SATscore
1111..........PennState...1170
1111..........NYU.........1170

I made this example up but no matter how many different tables I try this with...it always works the same way woth hold files, ie it returns each individual item in H1 but only pairs it to the FIRST item it finds in H2.

What the heck am I doing wrong! THANKS!

I mean it a basic join....?

This message has been edited. Last edited by: <Kathryn Henning>,

can we at least see the Join?
Are you joining to ALL

JOIN
LEFT_OUTER H1.H1.StudentId IN H1 TO UNIQUE H2.H2.StudentId IN H2
TAG J1 AS J1
END

First, H2 must be indexed on StudentId. Second, why are you using UNIQUE in the JOIN statement? Don't you need to JOIN to ALL occurrences in H2?

I am using the GUI interface. It shows each hold file and shows StudentId as a key in each.

I let it generate the join code buy using the gui. Join Type =Single Instance. (I tried Multiple - mad no difference in what was returned...said to use SINGLE in class!) I I also clicked the Left Outer Join radial button.

You definitely want multiple in that situation so that you get both, or all, of the cross referenced rows that match the host. Not sure why it is not working for you without seeing more.

Richard,

quote:

JOIN
LEFT_OUTER H1.H1.StudentId IN H1 TO UNIQUE H2.H2.StudentId IN H2
TAG J1 AS J1
END

This is the command you need:

  
JOIN
H1.H1.StudentId IN H1 TO ALL H2.H2.StudentId IN H2
TAG J1 AS J1
END

LEFT_OUTER is used especially for SQL databases.
TO ALL is used for sequential files (HOLD), FOCUS files and legacy files.
You can also use the newer syntax: TO MULTIPLE.

Thanks, I will try this.

I'm beginning to think the whole GUI interface is sort of a waste of time....at the very least the product misleads one into thinking you can do all this wonderful stuff but it seams you need to know how the product behaves and various syntax....I wish they had a class for that!

They do. Or buy/download some manuals.

It's a huge product. Trying to allow for every possible nuance of syntax and every possible technique is impossible

The gui works well in this case, but a GUI is not some magic powder. You have to know what you want.
If you post the query that creates the hold files it might be helpfully

I certainly hope you don't truly believe the GUI is a waste of time. It is a powerful interface with some well positioned training. We do have training classes in the underlying syntax. Check out education.informationbuilders.com. Will you be at Summit? Would love to chat with you!

RichardF -

It seems like you have stumbled across a rather subtle issue early on in your WebFOCUS coding career, but do not be discouraged. The reason you are not getting the Cartesian Cross Product you are expecting is because standard HOLD files are processed in a linear fashion, so the internal processor cannot go back and grab the SAT scores a second time to give you all 4 records you are expecting.

However, there are solutions. Here are two approaches you may use. Typically I use HOLD FORMAT FOCUS with an INDEX, but depending on your data and what other things your program is doing you may prefer the multi-path HOLD file approach. Notice that I am using the WebFOCUS sample CAR file here. In the future, it is recommended that you use one of the standard WebFOCUS demo files to illustrate any problems you are having. This makes it easier for others to reproduce your problem.

Anyway, here is some sample code you can play around with

APP PREPENDPATH ibisamp
-RUN

-*  Sample data that illustrates the issue
TABLE FILE CAR
   PRINT
      CAR
   BY COUNTRY
   WHERE (COUNTRY EQ 'JAPAN') ;
END
-RUN
TABLE FILE CAR
   PRINT
      MODEL
   BY COUNTRY
   WHERE (COUNTRY EQ 'JAPAN') ;
END
-RUN

-*  Create Hold files H1 & H2
TABLE FILE CAR
   PRINT
      CAR
   BY COUNTRY
   WHERE (COUNTRY EQ 'JAPAN') ;
ON TABLE HOLD AS H1
END
-RUN

TABLE FILE CAR
   PRINT
      MODEL
   BY COUNTRY
   WHERE (COUNTRY EQ 'JAPAN') ;
ON TABLE HOLD AS H2
END
-RUN

-*  You would expect this to do cartesian cross product but it doesn't
-*  This is because CARTESIAN=ON only applies to multi-path file structures
-*  Hold files are processed in a linear fashion
SET CARTESIAN = ON
JOIN COUNTRY IN H1 TO ALL COUNTRY IN H2  AS J1
CHECK FILE H1 PICT
-RUN
TABLE FILE H1
   PRINT *
END
-RUN

-*  Now we are creating a multi-path file structure
JOIN CLEAR *
TABLE FILE CAR
   BY COUNTRY
   WHERE (COUNTRY EQ 'JAPAN') ;
ON TABLE HOLD AS H0
END
-RUN

JOIN CLEAR *
JOIN COUNTRY IN H0 TO ALL COUNTRY IN H1 AS J1
JOIN COUNTRY IN H0 TO ALL COUNTRY IN H2 AS J2
CHECK FILE H0 PICT
-RUN

-*  So we see the cross product
TABLE FILE H0
   PRINT COUNTRY CAR MODEL
END
-RUN


-*  Alternatively we can create an indexed hold file
TABLE FILE CAR
   PRINT
      COUNTRY
      MODEL
   WHERE (COUNTRY EQ 'JAPAN') ;
ON TABLE HOLD AS H2 FORMAT FOCUS INDEX COUNTRY
END
-RUN

-*  This gives cartesian cross product even though we have set cartesian = OFF
-*  CARTESIAN=ON | OFF does not apply in this situation because we have a single path.
-*  Nonetheless, this works because the "TO" file now has an index so it is not processed in a linear fashion
SET CARTESIAN = OFF
JOIN CLEAR *
JOIN COUNTRY IN H1 TO ALL COUNTRY IN H2  AS J1
CHECK FILE H1 PICT
-RUN
TABLE FILE H1
   PRINT COUNTRY CAR MODEL
END
-RUN

  

EricH

Well I just learned something.

quote:

ON TABLE HOLD AS H2 FORMAT FOCUS INDEX COUNTRY

I've been using HOLD files forever of course and dealing with the 'file in wrong sort order' issue by re-sorting before joining, when all I had to do is HOLD AS X FORMAT FOCUS INDEX Y.

The big problem with FOCUS DBs is that you cannot use a multi-field join for the cross referenced FOCUS DB:

quote:

When you are joining two FOCUS data sources you can specify up to four alphanumeric fields in the host file that, if concatenated, contain values shared with the cross-referenced file. You may not specify more than one field in the cross-referenced file when the suffix of the file is FOC. For example, assume the cross-referenced file contains a phone number field with an area code-prefix-exchange format. The host file has an area code field, a prefix field, and an exchange field. You can specify these three fields to join them to the phone number field in the cross-referenced file. The JOIN command treats the three fields as one. Other data sources do not have this restriction on the cross-referenced file.
For data adapters that support multi-field and concatenated joins, you can specify up to 16 fields. See your data adapter documentation for specific information about supported join features. Note that FOCUS data sources do not support these joins.

I just don't understand why these restrictions exist for something that's called a "database", when they don't for flat files (as long as the data is in the correct sequence).

I think this is the same for XFOCUS databases too.

You can do that with a "conditional join":

quote:

Using conditional JOIN syntax, you can establish joins based on conditions other than equality between fields. In addition, the host and cross-referenced join fields do not have to contain matching formats, and the cross-referenced field does not have to be indexed.

The conditional join is supported for FOCUS and for VSAM, ADABAS, IMS, and all relational data sources. Because each data source differs in its ability to handle complex WHERE criteria, the optimization of the conditional JOIN syntax differs depending on the specific data sources involved in the join and the complexity of the WHERE criteria.

The standard ? JOIN command lists every join currently in effect, and indicates any that are based on WHERE criteria.



Syntax: How to Create a Conditional JOIN

The syntax of the conditional (WHERE-based) JOIN command is
JOIN [LEFT_OUTER|INNER] FILE hostfile AT hfld1 [WITH hfld2] [TAG tag1]
TO {UNIQUE|MULTIPLE}
FILE crfile AT crfld [TAG tag2] [AS joinname]
[WHERE expression1;
[WHERE expression2;
...]
END

url: http://ecl.informationbuilders...ource%2Ftopic139.htm

Unbalanced brackets on the WHERE clauses, but you get the idea.

Actually there's nothing 'conditional' about such a join, any more than a Table request containing a Where clause is a 'conditional report'. "Condition-based join" would be a more accurate description.

Terminology aside, this type of join can be used to declare a multiple-field equijoin between Focus (or Xfocus) files.

Jacob, that's a revelation, thanks.

WOW! I was out a while but back. I will have to absorb all this. Forgive me but this seems to be real basic stuff thats seems waaaaay to complicated! I mean if MsAccess can do it in a one line query....

So if I were joining non HOLD tables..ie master files...then this is NOT an issue?

btw....thanks for all the follow posts...I'll need to spend some quiet time with them. THe first offered solution:
JOIN
H1.H1.StudentId IN H1 TO ALL H2.H2.StudentId IN H2
TAG J1 AS J1
END



did not work....it was worth a shot!

LET CALL IT SOLVED.

Been in and out of the product as time allows...learned a bit about how finicky the product can be. Ive gotten what I need so far but what seems so simple is tricky.

Thanks for the help.