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..!!