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?

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

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

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.

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