Question { CSC, 6476 }

when we dont use aggregator in mapping ?

Answer

Aggregator transformation is used to calculate values in a
group. It is the only t/r which can be used for aggregate
calculations e.g: Sum,avg,max,min,count....

So when we don't need any such aggregate calculations, than
we don't use aggregator transformation.

Question { Sonata, 6237 }

how the data will be loaded while desiging the schema?which
one first (for e.g.-dimesions and facts)

Answer

The order of loading the schema is as below:

1)Dimension Table
2)fact table

Question { IBM, 6905 }

what is incremental data aggregation?

Answer

Incremental aggregation means applying only the captured
changes in the source to aggregate calculations in a session.
When the source changes only incrementally and if we can
capture those changes,then we can configure the session to
process only those changes.This allows informatica server to
update target table incrementally,rather than forcing it to
process the entire source and recalculate the same
calculations each time you run the session.By doing this
obviously the session performance increases.

Question { Patni, 5919 }

What is synonym?

Answer

A synonym is nothing but a permanent alias name given to an
existing data base table.

The main concept of synonym is as below;
1)To hide the original table name.
2)To hide the owner of the table.

Synonym can be created on following 3 objects:
1)Table
2)View
3)Synonym

Synonym are of basically 2 types:
1)Public synonym: This can only be created by a data base
administrator.
2)Private synonym: This can be created by any valid data
base user.

Syntax for creating a synonym is

create [or replace] synonym synonym_name for object name;

Question { Keane India Ltd, 20853 }

What is the query to find nth highest salary?
What is the use of cursors?

Answer

There are 3 ways to find out the nth highest salary in a
given table (e.g. emp) as below;

1) select distinct sal from emp e1 where &n=(select count
distinct sal from emp e2 where e1.sal <=e2.sal);


2) select empno,enaame,sal,deptno,rank () over (order by sal
desc) as ra from emp where ra=&n;

3) select empno,enaame,sal,deptno,dense_rank () over (order
by sal desc) as ra from emp where ra=&n;


All the above methods are tried n tested....it works
successfully.




What is a Cursor.

When a query is executed in oracle, a result set is produced
and stored in the memory. Oracle allows the programmer to
access this result set in the memory through cursors.

Why use a cursor?

Many times, when a query returns more than one row as a
result, we might want to go through each row and process the
data in different way for them. Cursor is handy here.

Types of cursors:

Oracle PL/SQL declares a cursor implicitly for all queries
and DML statements (including queries that return only one
row). But in most of the cases we don’t use these cursors
for queries that return one row. Based on this, there are 2
types of cursors

1. Explicit cursor
2. Implicit cursor

Question { IBM, 4229 }

explain about lookup t/r?

Answer

Look up t/r is used in a mapping to look up data in a
relational table,flat file,view or synonym.

The informatica server queries the look up source based on
the look up ports in the transformation.It compares look up
t/r port values to look up source column values based on the
look up condition.
Look up t/r is used to perform the below mentioned tasks:

1) To get a related value.
2) To perform a calculation.
3) To update SCD tables.

Question { IBM, 3991 }

explain abt joiner t/r?

Answer

Joiner transformation is used to join source data from two
related heterogeneous sources. However this can also be used
to join data from the same source. Joiner t/r join sources
with at least one matching column. It uses a condition that
matches one or more pair of columns between the 2 sources.
To configure a Joiner t/r various settings that we do
are as below:
1)Master and detail source
2)Type of join
3)Condition of the join

Question { IBM, 4410 }

explain abt union t/r?

Answer

A union transformation is a multiple input group
transformation,which is used to merge the data from multiple
sources similar to UNION All SQL statements to combine the
results from 2 or more sql statements.
Similar to UNION All statement, the union transformation
doesn't remove duplicate rows.

It is an active transformation.

Question { IBM, 8167 }

how do u fnd the duplicate rows and how to delete the
duplicate rows?

Answer

Hi Sankar...
The methods as told by u r correct but i guess sree has
asked about finding duplicate records and the way to delete
them in oracle not in informatica.

So, the query to find out the duplicate records in a given
table (e.g. emp table) is as below:

select * from emp where empno in (select empno from emp
group by empno where count(*) >1);

Explanation:
============
See, in EMP table there are total 14 records out of which
only empno is unique,rest all fields can be repeated, so the
empno which are repeated more than once are duplicate
records & the above query shows the same.

Query to delete duplicate records;

delete from emp where rowid not in (select max(rowid) from
emp group by empno);

OR

delete from emp where rowid not in (select min(rowid) from
emp group by empno);

OR

delete from emp e1 where rowid < (select max(rowid) from emp
e2 where e1.empno=e2.empno);

OR

delete from emp e1 where rowid > (select min(rowid) from emp
e2 where e1.empno=e2.empno);

Question { Wipro, 6276 }

source table have single column single record having with
single space.
load that source record into trg . trgt having two columns
and the
SOURCE TABLE LIKE

COL1
BHANU PRASAD
TRGT TABLE
COL1 COL2
IN THAT LOAD THE RECORD IN THE trgt table LIKE
COL1 COL2
BHANU PRASAD
HOW ? TELL ME PLZ

Answer

Let our source table name is "Name",which is having COL1 and
data in it is BHANU PRASAD (For easier understanding instead
of space between bhanu and prasad,lets take a underscore
i.e. BHANU_PRASAD.

Source ----> Exp t/r ----> target
In the exp t/r take "name" as an input port only and create
2 out put ports as COL1 and COL2.
In COL1 give condition
substr(Name,1,instr(Name,'_',1)-1)

In COL2 give condition
substr(Name,instr(Name,'_',1)+1)


now connect COL1 port form exp to COL1 port in tgt and same
for COL2, than the out put in the target table will be as below:

COL1 COL2
BHANU PRASAD

Question { IBM, 6245 }

what is the significance of newlookup port in dynamic look up

Answer

When we configure a look up t/r to use a dynamic cache, the
designer automatically adds a port called "New Lookup Row"
to the look up t/r. This port indicates with a numeric value
whether the Informatica server inserts or updates the row in
the cache or makes no change in the cache.

To keep the lookup cache and the target table
synchronised, we pass rows to the target when the New Lookup
Row value is equal to 1 or 2.

Question { Cap Gemini, 14087 }

In EMP table, for those emp whose Hiredate is same, update
their sal by "sal+500" or else for others keep the sal as it
is, how to do it by SQL query

Answer

Hi Sanjay,
the query u posted is a bit wrong,1st u have to do group by
n then having clause comes n not vise versa. so the final
query will be as below:

UPDATE emp
SET sal=sal+500
WHERE hiredate IN (SELECT hiredate
FROM employees
GROUP BY hiredate
HAVING COUNT(hiredate)>1
)


Hi Srinu,
It doesn't matter whether u write count(*) or
count(hiredate),the answer will always the same but its
better to give count(hiredate) only as it is easier to
understand.