can we over-ride sql over-ride at re-usable Source Qualifier at session level ?
Ans: You can not create re-usable source qualifier tranformation. If you have a sq tranformation, you can over-ride its sql query at session level.
Suppose you have un-connected lookup, having lookup override as below
SELECT DEPARTMENTSBKP.DEPARTMENT_NAME as DEPARTMENT_NAME, DEPARTMENTSBKP.DEPARTMENT_ID as DEPARTMENT_ID FROM DEPARTMENTSBKP
where DEPARTMENTSBKP.DEPARTMENT_ID = 20
You used this un-connected lookup at Mapping and there you changed the query to
SELECT DEPARTMENTSBKP.DEPARTMENT_NAME as DEPARTMENT_NAME, DEPARTMENTSBKP.DEPARTMENT_ID as DEPARTMENT_ID FROM DEPARTMENTSBKP
where DEPARTMENTSBKP.DEPARTMENT_ID = 10
When you run the session, always over-ridden query (that is dep = 10) will get executed. You can change the conditions, and some properties at mapping level of re-usable lookup. You can not change existing ports at mapping level. if you want to change the ports, you need to go back to Tranformation Developer.
In case of aggregator, if we do not select any port as a group by what will be the output? In my case only 1 rows got through...
What will happen if we do not use aggregate function and only 2-3 grup by ports, what is the output and how it works exactly ?
Please refer below comments for the answers of aggregation at Informatica.
Ans: You can not create re-usable source qualifier tranformation. If you have a sq tranformation, you can over-ride its sql query at session level.
Suppose you have un-connected lookup, having lookup override as below
SELECT DEPARTMENTSBKP.DEPARTMENT_NAME as DEPARTMENT_NAME, DEPARTMENTSBKP.DEPARTMENT_ID as DEPARTMENT_ID FROM DEPARTMENTSBKP
where DEPARTMENTSBKP.DEPARTMENT_ID = 20
You used this un-connected lookup at Mapping and there you changed the query to
SELECT DEPARTMENTSBKP.DEPARTMENT_NAME as DEPARTMENT_NAME, DEPARTMENTSBKP.DEPARTMENT_ID as DEPARTMENT_ID FROM DEPARTMENTSBKP
where DEPARTMENTSBKP.DEPARTMENT_ID = 10
When you run the session, always over-ridden query (that is dep = 10) will get executed. You can change the conditions, and some properties at mapping level of re-usable lookup. You can not change existing ports at mapping level. if you want to change the ports, you need to go back to Tranformation Developer.
In case of aggregator, if we do not select any port as a group by what will be the output? In my case only 1 rows got through...
What will happen if we do not use aggregate function and only 2-3 grup by ports, what is the output and how it works exactly ?
Please refer below comments for the answers of aggregation at Informatica.
1. When u check nothing, only 1 row get inserted which is last one
ReplyDelete2. on grouping by id, 3 rows got inserted
3. 2 group by, id and name, 3 got inserted.. no null at source
4
Input
ID NAME GES_UPDATE_DATE
1 mandar 1/8/2009 4:25:14 AM
2 sudhir 1/8/2009 4:25:26 AM
3 ashish 1/8/2009 4:25:34 AM
4 null 1/8/2009 4:33:40 AM
5 null 1/8/2009 4:33:52 AM
5 null 1/8/2009 4:34:02 AM
Output
ID NAME EFFECTIVE_START_DATE
2 sudhir 1/8/2009 4:25:26 AM
3 ashish 1/8/2009 4:25:34 AM
4 null 1/8/2009 4:33:40 AM
5 null 1/8/2009 4:34:02 AM
1 mandar 1/8/2009 4:25:14 AM
5
ID NAME GES_UPDATE_DATE
1 mandar 1/8/2009 4:25:14 AM
2 sudhir 1/8/2009 4:25:26 AM
3 ashish 1/8/2009 4:25:34 AM
4 null 1/8/2009 4:33:40 AM
5 null 1/8/2009 4:33:52 AM
5 null 1/8/2009 4:34:02 AM
null null 1/8/2009 4:37:12 AM
ID NAME EFFECTIVE_START_DATE
2 sudhir 1/8/2009 4:25:26 AM
3 ashish 1/8/2009 4:25:34 AM
4 null 1/8/2009 4:33:40 AM
5 null 1/8/2009 4:34:02 AM
1 mandar 1/8/2009 4:25:14 AM
null null 1/8/2009 4:37:12 AM
6
ID NAME GES_UPDATE_DATE
1 mandar 1/8/2009 4:25:14 AM
2 sudhir 1/8/2009 4:25:26 AM
3 ashish 1/8/2009 4:25:34 AM
4 null 1/8/2009 4:33:40 AM
5 null 1/8/2009 4:33:52 AM
5 null 1/8/2009 4:34:02 AM
null null 1/8/2009 4:37:12 AM
null null 1/8/2009 4:38:10 AM
ID NAME EFFECTIVE_START_DATE
2 sudhir 1/8/2009 4:25:26 AM
3 ashish 1/8/2009 4:25:34 AM
4 null 1/8/2009 4:33:40 AM
5 null 1/8/2009 4:34:02 AM
1 mandar 1/8/2009 4:25:14 AM
null null 1/8/2009 4:38:10 AM
For 1, 2 and 3 the input data is
ReplyDeleteID NAME GES_UPDATE_DATE
1 mandar 1/8/2009 4:25:14 AM
2 sudhir 1/8/2009 4:25:26 AM
3 ashish 1/8/2009 4:25:34 AM
--
If you check at Point#6, group by port is only ID. When there are more than 1 ids are present, and if you have selected that port as group by, only 1 row (which is last in db) will get passed. So, group by will restrict duplicate rows by default.
It will consider null also as a group and pass only 1 null row ahead.