Informatica Reference

Archive for the ‘Differences’ Category

DECODE
IIF
Decode can be used in Select statement
IIF cannot be used in a Select statement.
EXAMPLE:
DECODE(deptname,”SALES”,1,0)
—-If deptname is SALES then return 1 else return zero
EXAMPLE:IIF( GRADE > 1, IIF( GRADE < 2, SAL1, IIF( GRADE < 3, SAL2, IIF( GRADE < 4, SAL3, BONUS))), 0 )
—-If the GRADE less than ONE then return zero else return SAL1 OR SAL2 OR SAL3 OR BONUS
Decode gives better readablity to users
IIF dosenot give good readablity
Advertisements
JOINER
UNION
Using joiner we can remove duplicate rows
Union will not remove duplicate rows
Joiner can be Normal,Right Outer,Left Outer,Full Outer Join
Union is equlivalent to UNION ALL in SQL
In Joiner we have one input group and one output group
In Union we have multiple input groups and one output group.
Joiner implemented by using Joiner Transformation in Informatica.
Union implemented by using Union Transformation in Informatica
Joiner Transformation combines data record horizontally based on a join condition
Union Transformation combines data record vertically from multiple sources
Joiner supports both homegenousand heterogenous
Union also supports hetregenous(different sources)
Aggregator
Sorter
Aggregator transformation performs aggregate calculations, such as averages and sums.
Sorter Transformation is used to sort the data
Aggregator uses aggregate cache  to perform aggregate calculations
Sorter uses Sorter cache to perform the sort operation
Aggregation done by  using aggregate expression with conditional clauses,
Sorting is done based on the sort key and the order of sort key
Aggregator uses Incremental aggregation to perform aggregation calculations incrementally.
There is nothing called incremental aggregation in sorter
Sorter
Rank
Sorter is used to Sort the data either ASC or DSC
Rank is used to arrange data from top or bottom Group by can be done using Rank
Sorter can be used to remove duplicates(Use Distinct ouptut)
We can remove duplicates using Rank
In sorter we cannot assign values to the ports
In rank we can assign variables and write non-aggregate expressions also.
In sorter the Integration Service uses Sorter Cache to perform the sort operation.
In Rank the Integration Service stores group information in an index cache and row data in a data cache.
          Drop             Delete            Truncate
1. Whole structure and data will drop.

2. Can’t  Rollback

3. Can’t use where clause

4. All data and structure drop simultaneously.

5. Drop is a DDL Statement

1. Structure will not drop, data will drop.

2. Can Rollback

3. Can use WHERE clause

4. Row by Row data Delete

 

5. Delete is a DML Statement

1. Structure will not drop data will drop.

2. Can’t Rollback

3. Can’t use WHERE clause

4. Only all data will drop, truncate operation drop and recreate the table.

5. Truncate is a DDL Statement

                       Row id                            RowNum
1. Physical address of the rows.

2. Rowid is permanent

3. Rowid is 16-bit hexadecimal

4. Rowid gives address of rows or records

5. Rowid is automatically generated unique id of a row and it is generated at the time of insertion of row.

6. ROWID is the fastest means of accessing data.

7. They are unique identifiers for the any row in a table.

1. Rownum is the sequential number, allocated to each returned row during query execution.

2. Rownum is temporary.

3. Rownum is numeric

4. Rownum gives count of records

5. Rownum is a dynamic value automatically retrieved along with select statement output.

6. It represents the sequential order in which Oracle has retrieved the row.

Stored Procedure
Functions
Stored procedure may or may not return values. Function should return at least one output parameter. Can return more than one parameter using OUT argument.
Stored procedure can be used to solve the business logic. Function can be used to calculations
Stored procedure is a pre-compiled statement. But function is not a pre-compiled statement.
Stored procedure accepts more than one argument. Whereas function does not accept arguments.
Stored procedures are mainly used to process the tasks. Functions are mainly used to compute values
Cannot be invoked from SQL statements. E.g. SELECT Can be invoked form SQL statements e.g. SELECT
Can affect the state of database using commit. Cannot affect the state of database.
Stored as a pseudo-code in database i.e. compiled form. Parsed and compiled at runtime.