Stored Procedure A stored procedure is a precompiled collection of Transact-SQL, PL-SQL or other database procedural statements and optional flow control statements, similar to an executable script. Stored procedures are used to automate tasks that are too complicated for standard SQL statements. Stored procedures are stored and run within the database. The stored procedure must… Continue reading Stored Procedure transformation in Informatica
Tag: Transformations
How To Propagate Port Attributes
Propagating Port Attributes When you edit a port name in a transformation, by default, the Designer propagates references to that port in the expressions, conditions, and other ports in that transformation. You can also propagate changed attributes throughout the mapping. The Designer propagates ports, expressions, and conditions. We can propagate changes forward, backward, or in… Continue reading How To Propagate Port Attributes
How to Convert Julian to Gregorian date in Informatica
How to Convert Julian date to Gregorian date in Informatica Dates in the Julian calendar are called Julian dates which are not supported in Informatica. We need to convert the date to Gregorian calendar for further informatica processing. We can use TO_DATE function to convert the Julian date to Gregorian in an expression and then… Continue reading How to Convert Julian to Gregorian date in Informatica
Informatica certification – Transformations
Working with Transformations – My Notes — Transformation Overview — Which is the repository object that generates, modifies, or passes data? Ans:- A Transformation. Which transformation performs calculations on groups of data? Ans:- Aggregator Transformation. What are the actions performed by Active Transformations? Ans:- Change the number of rows that pass through the transformation, Change… Continue reading Informatica certification – Transformations
How to remove duplicate records using Sorter Transformation
How to remove duplicate records using Sorter Transformation In the last two post we have seen how to remove duplicate records (Only one column) using Aggregator and Expression. We have identified the duplicate JOB_ID and skipped those rows from loading to target. The same logic CANNOT be done using Sorter Transformation. In sorter we cannot… Continue reading How to remove duplicate records using Sorter Transformation
How to load every 5th record into target table
How to load every 5th record into target table We need to read the source table and load only 1st and every 5th (nth) records to target. Let’s start the mapping for this. We need a sequence generator and a filter transformations to complete this requirement. The logic which we are following here is to assign… Continue reading How to load every 5th record into target table
How to remove duplicate records using Expression
How to remove duplicate records using Expression In the last post we have used Aggregator Transformation to remove the duplicate records. In this post we will use the Expression transformation to remove the duplicate JOB_ID from the source Flat file. Source – Flat File Target – Oracle Table Key port – JOB_ID Transformations Sorter – To… Continue reading How to remove duplicate records using Expression
How to remove duplicate records Using Aggregator
How to remove duplicate records Using Aggregator There are couple of options available in informatica to remove duplicate records from the source. For Relational Tables 1. Source Qualifier > ‘SELECT DISTINCT’ option 2. Source Qualifier > SQL override (Write your own Query) For Flat files or other sources Sorter > Aggregator Sorter > Expression >… Continue reading How to remove duplicate records Using Aggregator
How to Load only Half of the records from Source table
Load only Half of the records from Source table The requirement is to process only half of the records from the source table. To do this we need to calculate the total number of rows (TOTAL-COUNT) in the source table and then assign ROW-COUNT and exclude the rows if the ROW-COUNT <= (TOTAL-COUNT/2). Let’s design the mapping for this. We… Continue reading How to Load only Half of the records from Source table
Source Qualifier – Full Outer Join
Full Outer Join using Source Qualifier – ‘SQL Query’ Integration service support only Left & Right outer joins. We cannot perform Full outer join using ‘User Defined Join’ properties in SQ, you will get a database error. This can be done by using overriding default ‘SQL query‘ in SQ. Full Outer Join Query SELECT EMP_JOB1.JOB_ID,… Continue reading Source Qualifier – Full Outer Join
Source Qualifier – User Defined Join – Right Outer Join
How to join two tables – Source Qualifier – User Defined Join – Right Outer ** Source Qualifier Transformation is the most efficient way to join two tables in the same Database ** There are two options available in SQ to join multiple tables, One using ‘User Defined join’ property and the other using ‘SQL Query’. We can… Continue reading Source Qualifier – User Defined Join – Right Outer Join
Source Qualifier – User Defined Join – Left Outer Join
How to join two tables – Source Qualifier – User Defined Join – Left Outer ** Source Qualifier Transformation is the most efficient way to join two tables in the same Database ** There are two options available in SQ to join multiple tables, One using ‘User Defined join’ property and the other using ‘SQL Query’. We can… Continue reading Source Qualifier – User Defined Join – Left Outer Join
Source Qualifier – User Defined Join – Normal Join
How to join two tables – Source Qualifier – User Defined Join ** Source Qualifier Transformation is the most efficient way to join two tables in the same Database ** There are two options available in SQ to join multiple tables, One using ‘User Defined join’property and the other using ‘SQL Query’. We can create a join override… Continue reading Source Qualifier – User Defined Join – Normal Join
Joiner Transformation – Normal Join
Joiner Transformation – Normal Join Let’s create a Mapping with a Joiner Transformation – Normal Join. We are going to join two tables JOB_ID1 & JOB_ID2 and create a target with the ports from two tables. Normal Join With a normal join, the Integration Service discards all rows of data from the master and detail… Continue reading Joiner Transformation – Normal Join
Joiner Transformation Overview
Joiner Transformation Overview Use the Joiner transformation to join source data from two related heterogeneous sources residing in different locations or file systems. You can also join data from the same source. The Joiner transformation joins sources with at least one matching column. The Joiner transformation uses a condition that matches one or more pairs… Continue reading Joiner Transformation Overview