Informatica Scenario and SQL Scenario Question

Scenario:

Source data looks like below

 Target 1 (Event number of occurrences)

A has come 2 times in the source, it should go to Target 1

Target 2 (odd number of occurrences)

If source data repeats odd number of times then it should go to Target 2

Solution:

create table test_odd_even_src

(

col1 varchar2(3)

);

create table test_odd_tgt

(

col1 varchar2(3),

col2 int

);

create table test_even_tgt

(

col1 varchar2(3),

col2 int

)

;

insert into test_odd_even_src values('A');

insert into test_odd_even_src values('A');

insert into test_odd_even_src values('B');

insert into test_odd_even_src values('B');

insert into test_odd_even_src values('B');

insert into test_odd_even_src values('C');

insert into test_odd_even_src values('D');

Mapping Looks like this:

Aggregator Transformation:

Joiner:

Router Transformation:

Now try to insert 2 more records into source and run the session again (Before that truncate target tables)

insert into test_odd_even_src values('A');

insert into test_odd_even_src values('B');

Source Data after inserting 2 new records.

select * from test_even_tgt

select * from test_odd_tgt

SQL Scenario:

Source
id,color
a1,yellow
a2,unknown
a1,green
a3,unknown
a1,unknown
a2,blue

Target
id,color
a1,yellow
a1,green
a3,unknown
a2,blue

Data Preparation:

create table test_color_src

(

id varchar2(30),

color varchar2(30)

);

create table test_color_tgt

(

id varchar2(30),

color varchar2(30)

);

insert into test_color_src values('a1','yellow');

insert into test_color_src values('a2','unknown');

insert into test_color_src values('a1','green');

insert into test_color_src values('a3','unknown');

insert into test_color_src values('a1','unknown');

insert into test_color_src values('a2','blue');

Solution:

Source Data:

SQL Query and Its output:

select id,color from test_color_src where color <> 'unknown'

union

(select id,'unknown' from test_color_src where color = 'unknown'

MINUS

select id,'unknown' from test_color_src where color <> 'unknown'

)

Thanks for Reading my blog..!!

Informatica Scenario: How to get salary and commission as column values when they both mentioned in same column data in source with type?

SRC:

Source data looks like below. Here Type=S means salary and Type=C means commission. 



Target:

we need salary and commission should come as columns against empid. 



Mapping Flow:
Below is the SQL logic:

SELECT S.EMPID, S.AMT AS SAL, C.AMT AS COM
  FROM (SELECT *
          FROM test_emp_sal_com
         WHERE TYPE = 'S') S
       LEFT OUTER JOIN (SELECT *
                          FROM test_emp_sal_com
                         WHERE TYPE = 'C') C
          ON C.EMPID = S.EMPID

same logic has been implemented using Informatica and the below is the mapping data flow.

same logic can also be used by using lookup on source. 

Scenario based SQL Interview Question

I have come across the below scenario in one of the forums and I'm giving the solution here. 

SRC:
Have source data like below

Output should like below:

what is the SQL to get output as shown above?

Ans: This can be achieved using loops in SQL and below is the query: 

WITH t AS (SELECT col1, col2 v FROM src)
  SELECT CEIL (v) - i AS seq, col1,
         CASE WHEN (i = 0 AND CEIL (v) <> v) 
              THEN 1 + v - (CEIL (v) - i)
              ELSE 1
         END  AS COL2
  FROM t, 
    XMLTABLE ('for $i in 0 to xs:int(D) return $i'
    PASSING XMLELEMENT (d, CEIL (v) - 1) COLUMNS i INTEGER PATH '.')
ORDER BY col1, CEIL (v) - i 



Please refer the below link to know more about loops: 
http://www.dba-oracle.com/t_loop_sql.htm

How to get previous row value in Informatica using expression transformation?

Source:

customers table:

Customers table has data for columns cust_id, Year, City columns like below.

Expected Target Value:

Prev city column need to be populated as the previous city like below.

Implementation:

Use order by columns CUST_ID and Year in source qualifier transformation or have sorter transformation to sort these 2 columns.

Connect the columns to expression transformation and create columns as below:

CUST_ID (i/o)= CUST_ID

YAER (i/o)= YAER

CITY (i/o)= CITY

v_cust_id (variable)=v_prev_Cust_ID

v_old_city (variable)=v_prev_city

o_prev_City (output)=IIF(v_cust_id=v_prev_Cust_ID,v_old_city,null)

v_prev_city (variable)=CITY

v_prev_Cust_ID (variable)=CUST_ID

Mapping will look like below: