user1220617
Reputation: 11
informatica aggregator router and expression
would like to ask i have a scenario to translate this sql query to the informatica mapping:
select v.id,
case when v.type = 21 then nvl(
(SELECT nvl(SUM(nvl(amount,0)),0)
FROM view1
WHERE ctype IN (252,82) AND id = v.id AND id2 = v.id2
GROUP BY id, id2) ,0)
else -1*nvl(
(SELECT nvl(SUM(nvl(amt,0)),0)
FROM view2
WHERE vtype IN (252,82) AND txn_id = v.txn_id AND txn_id2 = 1
GROUP BY txn_id, txn_id2),0) end AS amt1,
case when v.type = 21 then nvl(
(SELECT nvl(SUM(nvl(amount,0)),0)
FROM view1
WHERE ctype IN (253,105) AND id = v.id AND id2 = v.id2
GROUP BY id, id2),0)
else -1*nvl(
(SELECT nvl(SUM(nvl(amt,0)),0)
FROM view2
WHERE vtype IN (253,105) AND txn_id = v.txn_id AND txn_id2 = 1
GROUP BY txn_id, txn_id2),0)
end AS amt2,
case when v.type = 21 then nvl(
(SELECT nvl(SUM(nvl(amount,0)),0)
FROM view1
WHERE ctype IN (251,75,136,135,149,164) AND id = v.id AND id2 = v.id2
GROUP BY id, id2) ,0)
else -1*nvl(
(SELECT nvl(SUM(nvl(amt,0)),0)
FROM view2
WHERE vtype IN (251,75,136,135,149,164) AND txn_id = v.txn_id AND txn_id2 = 1
GROUP BY txn_id, txn_id2),0)
end AS amt3,
case when v.type = 21 then nvl(
(SELECT nvl(SUM(nvl(amount,0)),0)
FROM view1
WHERE ctype IN (6,101,148) AND id = v.id AND id2 = v.id2
GROUP BY id, id2) ,0)
else -1*nvl(
(SELECT nvl(SUM(nvl(amt,0)),0)
FROM view2
WHERE vtype IN (6,101,148) AND txn_id = v.txn_id AND txn_id2 = 1
GROUP BY txn_id, txn_id2),0) end AS amt4
from tblabc v, tblabc2 x
where v.status=2 and v.last_updated_datetime>sysdate
v.sid=x.sid (+)
How to code that in informatica?
First i use joiner from tblabc to view1 based on the id and id2 then
tblabc2 -> \
joiner 1 ----------------------------------------------------?
tblabc -> / |
| / -> aggregator for amt1
joiner2 --> router < -> aggregator for amt2
view1 -> / \ -> aggregator for amt3
\-> aggregator for amt4
view2???
but how to join back from the aggregator for amt1,2,3,4 to joiner 1?
I'm not able to link it back, when i drag the arrow, it didnt show the link, any idea?
Upvotes: 1
Views: 219
Answers (1)
Maciejg
Reputation: 3353
In this case, you're performing a self-join (as both pipelines originate from the same source). Before dragging ports to the joiner transformation, check the Sorted input property. And make sure both pipelines are indeed sorted on the keys - otherwise you'll get an execution error.
Upvotes: 1