Focal Point
Joining hold tables
April 04, 2007, 02:52 PM
<Riley>Joining hold tables
I need to create cartisean join.
In table A I have only one field with a list of numbers (values 1 thru 25)
In table B I have only one field with list of available territories.
How can I join to get them in another hold table with every number associated with every territory?
Is there a join all or outer join, something?
Thanks
April 04, 2007, 04:11 PM
Alan BI am not a relational person, but in pure FOCUS terms I would do:
DEFINE FILE CAR
BLANK/A1 WITH COUNTRY = ' ';
END
TABLE FILE CAR
PRINT COUNTRY
BY BLANK
ON TABLE HOLD FORMAT FOCUS INDEX BLANK
END
JOIN BLANK WITH EID IN EMPLOYEE TO ALL BLANK IN HOLD
DEFINE FILE EMPLOYEE
BLANK/A1 WITH EID = ' ';
END
TABLE FILE EMPLOYEE
PRINT
COUNTRY
BY EID
END
basically create a field containing a blank in on file and hold in a focus file indexing teh balnk field, Join the hold file to the other file on a defined field, also with a blank value, and table it all out.
Alan.
WF 7.705/8.007
April 04, 2007, 04:56 PM
susannahyou want a Cartesian product, it would seem.
file 1
a
b
c
d
file2
1
2
3
4
what you want is
a1
a2
a3
a4
b1
b2
b3
b4
etc.
Is that correct?
Classic use of the world famous IBI "MacGyver technique".
If you go to the regular ibi site, for documentation, you'll see a whole set of articles on MacGyver.
The term "outer join" is a Venn Diagram kind of concept...entirely different animal... not what you're talking about.
eg:
file 1
a
b
c
d
file2
b
c
d
e
result file
a stuff from file 1
b stuff from file 1 and 2
c stuff from file 1 and 2
d stuff from file 1 and 2
e stuff from file 2
| In Focus since 1979///7706m/5 ;wintel 2008/64;OAM security; Oracle db, ///MRE/BID |
April 04, 2007, 05:10 PM
Francis MarianiAn example of the McGyver technique:
SET HOLDLIST=PRINTONLY
-RUN
DEFINE FILE CAR
DUMMY/A1 WITH COUNTRY = '';
END
-RUN
TABLE FILE CAR
SUM
COUNTRY
BY DUMMY
BY COUNTRY
ON TABLE HOLD AS H001 FORMAT FOCUS INDEX DUMMY
END
-RUN
TABLE FILE CAR
SUM
CAR
BY DUMMY
BY CAR
ON TABLE HOLD AS H002 FORMAT FOCUS INDEX DUMMY
END
-RUN
JOIN DUMMY IN H001 TO ALL DUMMY IN H002 AS J1
-RUN
TABLE FILE H001
PRINT
COUNTRY
CAR
BY COUNTRY NOPRINT
BY CAR NOPRINT
END
-RUN
Gives you this:
COUNTRY CAR
ENGLAND ALFA ROMEO
ENGLAND AUDI
ENGLAND BMW
ENGLAND DATSUN
ENGLAND JAGUAR
ENGLAND JENSEN
ENGLAND MASERATI
ENGLAND PEUGEOT
ENGLAND TOYOTA
ENGLAND TRIUMPH
FRANCE ALFA ROMEO
FRANCE AUDI
FRANCE BMW
FRANCE DATSUN
FRANCE JAGUAR
FRANCE JENSEN
FRANCE MASERATI
FRANCE PEUGEOT
FRANCE TOYOTA
FRANCE TRIUMPH
ITALY ALFA ROMEO
ITALY AUDI
ITALY BMW
ITALY DATSUN
ITALY JAGUAR
ITALY JENSEN
ITALY MASERATI
ITALY PEUGEOT
ITALY TOYOTA
ITALY TRIUMPH
JAPAN ALFA ROMEO
JAPAN AUDI
JAPAN BMW
JAPAN DATSUN
JAPAN JAGUAR
JAPAN JENSEN
JAPAN MASERATI
JAPAN PEUGEOT
JAPAN TOYOTA
JAPAN TRIUMPH
W GERMANY ALFA ROMEO
W GERMANY AUDI
W GERMANY BMW
W GERMANY DATSUN
W GERMANY JAGUAR
W GERMANY JENSEN
W GERMANY MASERATI
W GERMANY PEUGEOT
W GERMANY TOYOTA
W GERMANY TRIUMPH
Francis
Give me code, or give me retirement. In FOCUS since 1991
Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
April 05, 2007, 03:14 AM
Alan BFrancis
Why do you have the host file, H0001, extracted to a FOCUS file with an INDEX on the field to be used in the host join?
It seems an unnecessary step as only the join field in the x-referenced file needs to be indexed and the host file can remain in its original format.
Using a DEFINE based join for the original host file, as I showed in my example, would be the more efficient approach.
Alan.
WF 7.705/8.007
April 05, 2007, 10:47 AM
Francis MarianiAlan,
The forum topic title is "joining hold tables" - so I'm joining HOLD tables.
Francis
Give me code, or give me retirement. In FOCUS since 1991
Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
April 05, 2007, 11:57 AM
Alan BFrancis
Please. You are creating a JOIN between two FOCUS files with an unnecessary INDEX on the host which may lead some people to think you need an INDEX on the host to create a join.
Two HOLD files can be joined directly, but not with the data in this topic or to get the required result in this instance.
Alan.
WF 7.705/8.007