0

I'm try to create Transformation and need to merge two Database based on query like that by using Merge Join and I little bit confuse what should i filled in First Step, Second Step to Lookup for that each query format.

Query Format :

SELECT * FROM A a LEFT JOIN B b on a.value=b.value

SELECT * FROM A a LEFT JOIN B b on b.value=a.value
Rio Odestila
  • 125
  • 2
  • 19

2 Answers2

1

Multiple ways to do this. you can use TableInput Step and just simply write your query. No need to do anything else for implementing above query.

Helping Hand..
  • 2,430
  • 4
  • 32
  • 52
1

There are various way to do it.

  • Write the sql with the join in the Table input step. Quick an dirty solution if your table are in the same database, but do not tell a PDI expert you did it that way.

  • If you know there is only one B record for each A record, use a Lookup Stream Step. Very, very, very efficient. The Main flow is the A and the lookup step is B.

  • If you have many B records for each A records, use a Join Rows. Don't be afraid, you do not really make a Cartesian product, as you can put your condition a.value=b.value.

  • In the same situation, you can also make a Merge join. The first step is the step you write fist in the sql select statement.

AlainD
  • 6,187
  • 3
  • 17
  • 31
  • Thanks @AlainD, very helpful with that various way to solve it. – Rio Odestila Jul 05 '18 at 01:21
  • At second point in your answer, it's okay applying LEFT JOIN using Stream Lookup? What about the result? It's will like LEFT JOIN in query? Well, new case if I want merge two data from different kind of source like Between CSV and DB also applying those LEFT JOIN, is it possible? And – Rio Odestila Jul 05 '18 at 01:31
  • Yes, that the point of the Steam technology, you do not care from where the data comes in. And the difference between a LEFT JOIN (or the `join rows` step) and the `Stream Lookup` is that the former always join one record, which by experience is more than 80% of the case. – AlainD Jul 09 '18 at 17:29