In SQL, one can make a union query to combine the results of columns in different tables into one column in a new table. Example:
SELECT PIN From Table1
UNION SELECT ID FROM Table2
INTO NewTable;

Is it possible to do something similar in WebFOCUS. I have different columns in several tables that contain similar information. I need to combine it all in one column.

WebFocus uses the MORE command for this..
TABLE FILE A
PRINT X AND Y
BY Z
WHERE V EQ ETC.
MORE
FILE B
WHERE R EQ ETC.
END

IF THE FIELD NAMES ARE DIFFERENT IN THE TWO FILES
THEN DEFINE THEM FIRST SO THEY ARE THE SAME.. EG
DEFINE FILE B
X=MYXX;
Y=MYYY;
END
After the first query, only the WHERE clauses are
used as the structure is assumed to be the same.
Up to about 5 or 6 MORE groups can be used.
You can accumulate.. SUM X ..etc.

You could do a MATCH FILE also.

MATCH FILE table1
PRINT
field1
field2
field3
BY field4
RUN
FILE table2
PRINT
field1
field3
field6
field7
BY field4
AFTER MATCH HOLD AS hold_file OLD-OR-NEW
END

JOIN is the classic way to do this.
your two files have only their keys in common so the MORE syntax doesn't apply.
MATCH works.
But JOIN is a more direct way.
JOIN KEY1 IN table1 TO KEY1 IN table2 AS J1
where the keys are the same.
the keys do not have to be named the same.
JOIN KEY1 IN table1 to KEY2 IN table2 ...etc
if they are identical in value and format, just not in name.
Read up on joins in the manual. you'll like it. Its a bit more complex, and has nuances.
Now, all variable in table2 are available to you as you read table1.
TABLE FILE TABLE1
PRINT PIN ID ...whatever
...
END

Could you guys also tell which one the ways would be the most efficient in processing??

Thanks.

If you're looking for efficiency, rule out the MATCH. My guess is the UNION or the JOIN would be best. You'll have to try!
Thanks!

Can't you just use a sql passthrough and use your sql code?
SQL
SELECT PIN From Table1
UNION SELECT ID FROM Table2;
TABLE ON TABLE HOLD AS NEWTABLE FORMAT FOCUS
END[/code]

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