Variable Ports in Informatica

Variable Ports in Informatica

A port which can store the data temporarily is known as the variable port in Informatica, which is represented as (V)

A variable port is required to "simplify the complex expression" it improves the performance of calculations

A variable ports are not visible in the "Normal view". Visible only in the "edit view".

Example

Design a mapping to calculate the gross salary

Gross salary = total sal +HRA-Tax

Total sal=sal+commision

HRA = Sal*0.14

tax = sal*0.12

Procedure

Create a source definition with the name emp

create a target definition with the name emp-gross sal

(mpno, ename, job, sal, comm, totalsal, HRA, tax, gross salary)

Create a mapping with the name m-emp-variable-port

drop the source and target definitions

create the transformation type expression

From SQ EMP copy the required ports to expression transformation double click on expression transformation select the ports tab

Portname	Datatype	Precession	Scale	I O V	Expression
V-total sal	decimal	7	2		IF(isnull(comm)), sal, sal+comm)
V-HRA	decimal	7	2		sal*0.14
V-Tax	decimal	7	2		sal*0.12
total sal	decimal	7	2		V-total sal
HRA	decimal	7	2		V-HRA
Tax	decimal	7	2		V-tax
Gross sal	decimal	7	2		V-totalsal+V.HRA.V-Tax

Click apply and click ok

Example

Design a mapping to calculate the year of join (YOJ) from hire date

Procedure

Create a source definition with the name EMP

Create a target definition with the name Emp-YOJ-N

(Empno, tname, job, hiredate, YoJ, saldeptno)

YOJ data type is number (p,s)

Create a mapping with the name m-employee-YOJ-N

Drop the source and target definitions

create the transformation type expression

from SQ-EMP copy the required ports to expression transformation double click on expression transformation select the ports tab from tool bar click and add a new port (:)

If you want to enrich your career and become a professional in Informatica, then visit Tekslate - a global online training platform: "Informatica Training" This course will help you to achieve excellence in this domain.

Port name           Data type   precession    sale    IOV    Expression

YOJ                                    decimal                      4                      0

To-decimal (To-chr(hiredate,'yyy')

click apply click ok

from express transformation connect to the ports to target

ex:-

design a mapping to decode the genders

0M

1F, unknown

Create the source table with the name customer in user account Scott

CID c name hinder

1001   cname      0

1002 cname        1

create a target table with the name T-customer (CID, Cname, hender)

the gender data type should be varchar2(1)

create the mapping with the name m-decode-gender

drop the source and target definition

create the transformation type expression

From source availed copy the ports to expression transformation double click on expression transformation select the ports tab uncheck the output port for a port name gender from toolbar click on add a new port []

Port name data type precession scale I O V     Expression

0-Gener      strain              1

decode (0.M,

1,F

'UNK')

or

IFF(Gender)=0,'M','F')

Click apply click ok

From expression, transformation connect the ports to target

ex:-

Design a mapping to calculate the year of experience from the Hire date

create a source definition with the name EMP

create a target definition with the name EMP-YOE

(empno, ename, job, hiredate, yoe, sal deptno)

The YOE data type is number (P,S)->5,2

create a mapping with the name m-employee-yoe

create the transformation type expression and develop the following type expression

Data-DIFF(SYSDATE, HIREDATE, 'YYYY')

Concatenating two string fields (comns):-

The two string fields can be concatenated using concat () function (or)ll

Recommended is 11 operator

Expression:- EFName 11 11Ename

phone :99999999

target: (999) - 9999-999

'C'II SubSTR (Phone, 1,3)II',''-'II substr (phone,4,4)II'-'

IIsubstr (Phone, 8,3)

For in-depth understanding of Informatica click on

• Informatica Tutorials & Interview Questions
• Introduction to Informatica
• Informatica Components
• Flat Files in Informatica
• Working with Tasks in Informatica
• Informatica Concepts