Queries in merging two tables

[Harsha3]

Good Day,

We need to perform a simple activity where I used outer join and merge . The purpose of merge is to actually unite both the tables to proceed further . Since the entries present in the result table are not in source table , there are null entries which are expected . I would like to replace all the null values with the values of the present in the source table . I provide screenshot for better clarity.

Table 1: ( Source table )

Table 2: ( After performing join)

Merged result (Merging Table 1 and Table 2 😞

Expected Output:

--Wanted all the null values exactly to be replaced with the value present in the first row . Is there any way to do the same.

Thanks,
Harshaavrdhana

[SpiroTaleski]

@Harsha3

Probably you can try with Outer join, putting ‘1’ for Left and Right Path.

Regards,
Spiro Taleski

[ptaylor]

Spiro_Taleski:

Probably you can try with Outer join, putting ‘1’ for Left and Right Path.

Don’t use Outer when doing a 1=1 join. Always use Inner. The result will be the same, by definition, but the performance, both memory-wise and time-wise, will be better with Inner. This advice is based on the details of the implementation of each of these joins.

[Harsha3]

Hi Ptylor,

Thanks for the details it actually worked !.

Thanks Taleski for the updates.

[ptaylor]

A Join with join type Merge doesn’t do a true join – it just merges documents from each input stream as your results show. But I’m confused by your example. Your Table 2 says “after performing join”. What does the data look like before performing the join that produced Table 2? Is there a value corresponding to User_Id from Table 1 that you could use for a true join? I’m guessing that a 1=1 is not what you really need – it will produce a full cross-product of both input streams. If you decide to use 1=1, set the join type to Inner, and arrange the Join so that the input with more documents (table 2) is the first input, and the input with a single document (table 1) is the second input.