0

I have to implement a SQL statment with more than one subquery in Informatica PowerCenter.

In one of this I am stuck because I would use as suggested in another answer,2 Source Qualifier and then a Joiner Component, but I need to have the between condition as follow:

TableB.columnID BETWEEN TableA.columnID AND TableB.column_ID

Unfortunately I did not yet found a way to implement this condition that I so have in a where statement as you can see from the complete SQL.

More as far I know as far I know in PowerCenter the SQL transformation component cannot get imput from 2 Source Qualifiers.

The solution so far that I have found is to put the between condition in a SQL Override in Source Qualifier like in the image:

The all SQL statment and my solution so far as a SQL Override:

SQL Override

Community
  • 1
  • 1
Carmine Tambascia
  • 1,628
  • 2
  • 18
  • 32

2 Answers2

1

Match criteria in joiner Can only be '='

If the join is not a 'full outer' you can use a lookup transformation configured to return ALL rows on match (not any, first or last)

Match criteria in lookups can be '>=', '<=', '<>' as well as'='.

There is a guide available here: https://dwbi.org/etl/informatica/139-active-lookup-transformation

Lars G Olsen
  • 1,093
  • 8
  • 11
0

If you choose to use 2 source qualifiers and a joiner transformation, you can apply the between condition afterwards in a Filter transformation. You would have an expression like below in the filter transformation.

TableB_ID >= TableA_VON_ID AND TableB_ID <= TableB_BIS_ID
Samik
  • 3,435
  • 2
  • 22
  • 27
  • This is a Solution that should only be used on very small amounts of data,since a chartesian product will exist in DTM buffer memory between the joiner and the filter. – Lars G Olsen Feb 22 '17 at 21:38