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,

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

How about SUM instead of PRINT?

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