Please pardon me if this question has been raised earlier. I want to merge three tables together and put it in a hold file, but for some reason following the standard syntax is not helping. Can someone identify what's wrong with what I'm doing?
TABLE FILE TABLE1 MORE FILE TABLE2 MORE FILE TABLE3 END
I expect TABLE1 to have merged rows of the TABLE1, TABLE2, TABLE3 in that order, but it keeps rows of only TABLE1.
Thanks in advance! ----------------------------------------------This message has been edited. Last edited by: Kerry,
June 16, 2009, 02:10 PM
GinnyJakes
There are a number of rules to follow to use MORE or Universal Concatenation.
The first thing I noticed is that you don't have a PRINT or SUM statement for your first table.
The second thing is that all of the column names to be printed in all 3 of the masters have to be the same.This message has been edited. Last edited by: GinnyJakes,
The PRINT display statement cannot be repeated for each file - it must be specified only once.
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
June 16, 2009, 02:30 PM
<Kev>
I tried above syntax with other tables, and it's working, so I'm guessing columns may not be compatible in my tables. So, - If columns are not compatible, does it simply ignore those tables? - Also, how do I find out which columns are not compatible?
Thanks!
---------------------------------------------
June 16, 2009, 02:48 PM
Francis Mariani
Kev,
The columns you're extracting from each of the tables must have the same name and format - if they're not the same name and format, you may define virtual fields to rename/reformat them.
If columns do not correspond you should get warning/error messages.
Take a look at the Master files (.mas) for each table, or run this code for each table:
?FF TABLE1
?FF TABLE2
...
This will display the column names. aliases and formats
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
June 17, 2009, 07:54 AM
trob
Kev, I would recommend coding it like this with only one print.
TABLE FILE TABLE1
PRINT COL1 COL2 COL3
WHERE COL3 LT 4
MORE
FILE TABLE2
MORE
FILE TABLE3
END
TABLE FILE TABLE1
PRINT COL1 COL2 COL3
WHERE COL3 LT 4
ON TABLE HOLD AS HOLITALL --- add this line
MORE
FILE TABLE2
PRINT COL1 COL2 COL3
MORE
FILE TABLE3
PRINT COL1 COL2 COL3
END
WF 7.6.11 Oracle WebSphere Windows NT-5.2 x86 32bit
June 17, 2009, 12:23 PM
RSquared
Sorry,
and one PRINT command. I copied the original code.
WF 7.6.11 Oracle WebSphere Windows NT-5.2 x86 32bit
June 18, 2009, 09:56 AM
<Kev>
I'm all set with this. Thanks for your guidance everyone!
June 18, 2009, 10:55 AM
j.gross
Note that selection criteria (WHERE and IF clauses) only apply to the section in which they are coded. If you want to impose WHERE COL3 LT 4 on all three FILE retrievals you have to repeat it:
TABLE FILE TABLE1
PRINT COL1 COL2 COL3
WHERE COL3 LT 4;
ON TABLE HOLD AS HOLITALL
MORE
FILE TABLE2
WHERE COL3 LT 4;
MORE
FILE TABLE3
WHERE COL3 LT 4;
END
