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: