"Joining" two tables for combined output

I have a user that is trying to consolidate the data from two tables. Both tables have identical columns. She attempted this through a JOIN but based on her requirements, this will not suffice. Table A has current data and Table B has historical data. What she wants is a report which lists all data from A and all data from B that matches her criteria in a combined view. In SQL I believe you can load a temporary table with the results from 2 separate queries and then execute a query on the temp. How can this be accomplished in WebFOCUS?

Posts: 1 | Registered: April 04, 2007

Danny-SRL

Virtuoso

posted

Hide Post

Michael,
Use the MORE subcommand of TABLE. The fields of both of your SQL tables must have the same names. If they don't use DEFINE to give table B the same names as table A.

TABLE FILE A
PRINT FIELD1 ... FIELDN
BY SORTFIELD1 ..
WHERE ....
MORE
FILE B
WHERE ...
END

Daniel
In Focus since 1982
wf 8.202M/Win10/IIS/SSA - WrapApp Front End for WF

Posts: 1980 | Location: Tel Aviv, Israel | Registered: March 23, 2006

Ignored post by Danny-SRL posted

Show Post

smiths

Platinum Member

posted

Hide Post

A method using APPEND....

FILEDEF MYFILE DISK MYFILE.FTM

TABLE FILE CAR
PRINT CAR BY COUNTRY
IF COUNTRY EQ 'JAPAN'
ON TABLE HOLD AS MYFILE
END

FILEDEF MYFILE DISK MYFILE.FTM (APPEND

TABLE FILE CAR
PRINT CAR BY COUNTRY
IF COUNTRY EQ 'W GERMANY'
ON TABLE HOLD AS MYFILE
END

TABLE FILE MYFILE
PRINT *
END

------------------------------------------------------------------------
PROD: WebFOCUS 7.6.2 on Unix AIX/Tomcat/Servlet Mode
TEST: WebFOCUS 7.6.2 on Unix AIX/Tomcat/Servlet Mode

Posts: 210 | Location: Ottawa | Registered: November 03, 2005

Ignored post by smiths posted

Show Post

Jessica Bottone

Platinum Member

posted

Hide Post

You can also use the MATCH command...

DEFINE FILE TABLE1
DUMMY1/A1 = '1';
END
DEFINE FILE TABLE2
DUMMY2/A2 = '2';
END
MATCH FILE TABLE1
PRINT DUMMY1
-*- put real fields from TABLE1 in these BY's
BY FIELD1
BY FIELD2
.
.
RUN
FILE TABLE2
PRINT DUMMY2
-*- put real fields from TABLE2 in these BY's
BY FIELD1
BY FIELD3 AS FIELD2
.
.
AFTER MATCH HOLD AS HOLDA OLD-OR-NEW
END
TABLE FILE HOLDA
-*- do your report here
END

It's the OLD-OR-NEW that will keep data from both tables. And just like with MORE, the field names between the two tables have to be the same except here, you don't have to use a DEFINE to make them the same - you can do it on the fly (FIELD3 AS FIELD2). I will say that I believe a MORE command will have better performance for you than a MATCH.

The only other suggestion I have for you is if these are RDBMS tables (and from your post, I believe they are), and you're comfortable with writing SQL, you could use SQL Pass-Thru and write your own SQL with a UNION command, hold that off then report from the hold.

Data Migrator 5.3, 7.1, 7.6
WebFOCUS 7.1, 7.6, 7.7
SQL Server, Oracle, DB2
Windows

Posts: 126 | Registered: January 18, 2007

Ignored post by Jessica Bottone posted

Show Post

FrankDutch

Virtuoso

posted

Hide Post

Michel

A good start here on this site is first search on the question you have.
Someone else posted the same question 2 or 3 days ago and got the same answers.

My answer would have been to create an union query on the sql server.

IMO the MATCH command is not the proper answer since that combines records from one table based on key fields from the other and gives you an unwanted output.

Select *
from tableone
union
select *
from tabletwo
;

(supposed the fields have the same layout
Frank

	*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

Show Post

Please Wait. Your request is being processed...

Read-Only Topic

Focal Point

Focal Point Forums

WebFOCUS/FOCUS Forum on Focal Point

"Joining" two tables for combined output

Focal Point | Privacy Statement | Terms of Service