February 10, 2009, 08:01 AM
Ajai [CLOSED] Report from two data sources
Hi,
We have a report which has six columns in it. The first three columns are taken from one datasource(TABLE A) and the next three columns are taken from another datasource(TABLE B). The two tables are joined on Column_1. I have given the datasource structure below.
TABLE-A TABLE-2
Column_1 Column_2 Column_1 Column_3
AAA S1 AAA R1
AAA S2 AAA R2
AAA R3
AAA R4
We used the below line to join the two tables.
JOIN Column_1 IN HOLD_1 TO MULTIPLE Column_1 IN HOLD_2 The output which we obtained is shown here.
Current_Status Previous_Status
S1 R1
S1 R2
S1 R3
S1 R4
S2 R1
S2 R2
S2 R3
S2 R4
But, we need an output as shown below
Current_Status Previous_Status
S1 R1
S2 R2
R3
R4
Please help us to find a solution for this.
Thankyou,
Ajai.
This message has been edited. Last edited by: Kerry , February 19, 2009 04:38 PM February 11, 2009, 11:00 AM
Rob Soors Maybe you can get the output with joins by turning the RDBMS optimalization of (SQL SQLMSS SET OPTIMIZATION OFF)
However, using MATCH files will do the thing..
MATCH FILE A
PRINT COLUMN_2
BY COLUMN_1
RUN
FILE B
PRINT COLUMN_3
BY COLUMN_1
AFTER MATCH HOLD OLD-OR-NEW
END
TABLE FILE HOLD
PRINT COLUMN_2 COLUMN_3
END
February 11, 2009, 11:09 AM
Francis Mariani How about SUM instead of PRINT?
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
February 20, 2009, 06:43 AM
Ajai Francis,
Some of the columns are having varchar values. Hence we cannot use the SUM command.
Rob,
We were not able to achieve the output using MATCH.
Thanks,
Ajai