I have two tables that do not have a common field. Like this:
TABLE FILE CAR PRINT COUNTRY -*ON TABLE HOLD AS TABLE2 END -* TABLE FILE CAR PRINT CAR -*ON TABLE HOLD AS TABLE1 END
What I need is to merge these two together so that the finished table will have each country value will be repeated for each car. So you would have:
Country | Car ENGLAND | JAGUAR ENGLAND | JENSEN ENGLAND | TRIUMPH ENGLAND | PEUGOT ENGLAND | ALPHA ROMEO ENGLAND | MASERATI ENGLAND | DATSUN ENGLAND | TOYOTA ENGLAND | AUDI ENGLAND | BMW FRANCE | JAGUAR FRANCE | JENSEN FRANCE | TRIUMPH FRANCE | PEUGOT FRANCE | ALPHA ROMEO FRANCE | MASERATI FRANCE | DATSUN FRANCE | TOYOTA FRANCE | AUDI FRANCE | BMW ... so on and so forth.
Any WF code I can use? If not, any SQL code I can use?This message has been edited. Last edited by: Mark1,
Windows version 768
July 29, 2010, 11:35 AM
Francis Mariani
Try this, a variation of the "McGyver Technique":
TABLE FILE CAR PRINT COUNTRY COMPUTE DUMMY1/A1 = ''; ON TABLE HOLD AS H001 FORMAT FOCUS INDEX DUMMY1 END -RUN
TABLE FILE CAR PRINT CAR COMPUTE DUMMY1/A1 = ''; ON TABLE HOLD AS H002 FORMAT FOCUS INDEX DUMMY1 END -RUN
JOIN DUMMY1 IN H001 TO ALL DUMMY1 IN H002 AS J1
TABLE FILE H001 PRINT COUNTRY CAR BY COUNTRY NOPRINT BY CAR NOPRINT END
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
July 29, 2010, 12:01 PM
T.Peters
Could you just do a cross join instead of a match?
WebFOCUS: 7702 O/S : Windows Data Migrator: 7702
July 29, 2010, 12:15 PM
Francis Mariani
A cross join is certainly more elegant, I've never done this before, but it works quite nicely:
SQL
SELECT
C1.COUNTRY,
C2.CAR
FROM CAR C1
CROSS JOIN CAR C2
ORDER BY
C1.COUNTRY,
C2.CAR
END
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
July 29, 2010, 12:41 PM
Mark1
Awesome!! Thank you so much!
Windows version 768
July 29, 2010, 05:57 PM
Waz
I think that this does the same thing.
JOIN FILE CAR AT COUNTRY TAG C1 TO ALL
FILE CAR AT CAR TAG C2 AS JC
END
TABLE FILE CAR
PRINT C1.COUNTRY
C2.CAR
END
Waz...
Prod:
WebFOCUS 7.6.10/8.1.04
Upgrade:
WebFOCUS 8.2.07
OS:
Linux
Outputs:
HTML, PDF, Excel, PPT
In Focus since 1984
Pity the lost knowledge of an old programmer!
March 07, 2013, 01:18 PM
bug
It's interesting that all above method only works when hold file is FORMAT FOCUS. If I didn't specify FORMAT FOCUS none of them works. The result either only takes the first row from left table to join all rows to the right, or only first row in both tables.
7.66 and 7.704 System: Windows / AIX / Linux Output: Mostly HTML, with some PDF, Excel and Lotus(!)
March 07, 2013, 03:50 PM
Waz
The conditional JOIN can't use FOCUS files, they must be non FOCUS hold files.