informatica multi correlated subquery implementati

Question

I am facing a task that due to my lack of experience with Informatica Components, in particular SQL Transformation, I did not implemented yet. So what would be the best approch in PowerCenter to implement this kind of subquery:

SELECT
   A.ID,
   NVL2(A.SACHKONTO, B.KLAMMER, A.ID) AS KLAMMER
FROM
Table1 A,
    (SELECT
       A.ID AS KLAMMER,
       B.ID
FROM
    (SELECT
        ID,
        ID AS VON_ID,
        LEAD(ID,1) OVER (ORDER BY ID) - 1 AS BIS_ID
    FROM
        Table1
    WHERE
        SACHKONTO IS NULL) A,
    Table1 B
WHERE
    B.ID BETWEEN A.VON_ID AND A.BIS_ID
) B
WHERE
  A.ID = B.ID

So I tried different approch with small successed. The first was to "decompose" the SQL in it's small part(I will refer if necessary or edit the question). I also tried to put the all query (after have adapted it to Informatica SQL "language", but without success.

This is the most close solution that i got to replicate such query, without considerting performance important(I did use an SQL Override in the SQ), but as from the table result, the join is been not propely processed, then I believe I need to add another pipeline to let execute the join in the proper order:

My mapping solution:

Answer 1

You could put the subquery into a lookup transformation and then match the records coming through from the outer query (which i assume will be what you're putting into your zource qualifier per the A.ID = B.ID from the original query and then filter the nulls later)

Or you could similarly use 2 source qualifiers (one for the parent query and another for the subquery) followed by a joiner transformation with normal join type. This way will filter your source data sooner than my first option so performance wise should be better.

Final option is to use a source qualifier with sql override same as your current query... filters record out right at the database so a plus there (so long as the query itself is optimal) but sql overrides are not validated in the wider mapping context and can spring unexpected surprises so should be used with a lot of caution.