There is a source table containing 2 columns Col1 and Col2 with data as follows:


Col1 Col2

a l

b p

a m

a n

b q

x y


Design a mapping to load a target table with following values from the above mentioned source:

Col1 Col2

a l,m,n

b p,q

x y



Solution:



Use a sorter transformation after the source qualifier to sort the values with col1 as key. Build an expression transformation with following ports(order of ports should also be the same):



1. Col1_prev : It will be a variable type port. Expression should contain a variable e.g val

2. Col1 : It will be Input/Output port from Sorter transformation

3. Col2 : It will be input port from sorter transformation

4. val : It will be a variable type port. Expression should contain Col1

5. Concatenated_value: It will be a variable type port. Expression should be decode(Col1,Col1_prev,Concatenated_value

','

Col2,Col1)

6. Concatenated_Final : It will be an outpur port conating the value of Concatenated_value



After expression, build a Aggregator Transformation. Bring ports Col1 and Concatenated_Final into aggregator. Group by Col1. Don't give any expression. This effectively will return the last row from each group.


Connect the ports Col1 and Concatenated_Final from aggregator to the target table.

Click Hear to Download SQL questions and answers

Download

Script for creating the tables in SCOTT/TIGER database(EMP, DEPT) tables with data

(copy the below script and save it as demobld.sql  in C:\ drive and then open the SQL prompt type like below
SQL>start C:\demobld.sql
copy from below line up to EXIT


-- Copyright (c) Oracle Corporation 1988, 2000.  All Rights Reserved.
--
-- NAME
--   demobld.sql
--
-- DESCRIPTION
--   This script creates the SQL*Plus demonstration tables in the
--   current schema.  It should be STARTed by each user wishing to
--   access the tables.  To remove the tables use the demodrop.sql
--   script.
--
--  USAGE
--    From within SQL*Plus, enter:
--        START demobld.sql

SET TERMOUT ON
PROMPT Building demonstration tables.  Please wait.
SET TERMOUT OFF

DROP TABLE EMP;
DROP TABLE DEPT;
DROP TABLE BONUS;
DROP TABLE SALGRADE;
DROP TABLE DUMMY;

CREATE TABLE EMP
    (EMPNO NUMBER(4) NOT NULL,
     ENAME VARCHAR2(10),
     JOB VARCHAR2(9),
     MGR NUMBER(4),
     HIREDATE DATE,
     SAL NUMBER(7, 2),
     COMM NUMBER(7, 2),
     DEPTNO NUMBER(2));

INSERT INTO EMP VALUES
     (7369, 'SMITH',  'CLERK',     7902,
     TO_DATE('17-DEC-1980', 'DD-MON-YYYY'),  800, NULL, 20);
INSERT INTO EMP VALUES
     (7499, 'ALLEN',  'SALESMAN',  7698,
     TO_DATE('20-FEB-1981', 'DD-MON-YYYY'), 1600,  300, 30);
INSERT INTO EMP VALUES
     (7521, 'WARD',   'SALESMAN',  7698,
     TO_DATE('22-FEB-1981', 'DD-MON-YYYY'), 1250,  500, 30);
INSERT INTO EMP VALUES
     (7566, 'JONES',  'MANAGER',   7839,
     TO_DATE('2-APR-1981', 'DD-MON-YYYY'),  2975, NULL, 20);
INSERT INTO EMP VALUES
     (7654, 'MARTIN', 'SALESMAN',  7698,
     TO_DATE('28-SEP-1981', 'DD-MON-YYYY'), 1250, 1400, 30);
INSERT INTO EMP VALUES
     (7698, 'BLAKE',  'MANAGER',   7839,
     TO_DATE('1-MAY-1981', 'DD-MON-YYYY'),  2850, NULL, 30);
INSERT INTO EMP VALUES
     (7782, 'CLARK',  'MANAGER',   7839,
     TO_DATE('9-JUN-1981', 'DD-MON-YYYY'),  2450, NULL, 10);
INSERT INTO EMP VALUES
     (7788, 'SCOTT',  'ANALYST',   7566,
     TO_DATE('09-DEC-1982', 'DD-MON-YYYY'), 3000, NULL, 20);
INSERT INTO EMP VALUES
     (7839, 'KING',   'PRESIDENT', NULL,
     TO_DATE('17-NOV-1981', 'DD-MON-YYYY'), 5000, NULL, 10);
INSERT INTO EMP VALUES
     (7844, 'TURNER', 'SALESMAN',  7698,
     TO_DATE('8-SEP-1981', 'DD-MON-YYYY'),  1500,    0, 30);
INSERT INTO EMP VALUES
     (7876, 'ADAMS',  'CLERK',     7788,
     TO_DATE('12-JAN-1983', 'DD-MON-YYYY'), 1100, NULL, 20);
INSERT INTO EMP VALUES
     (7900, 'JAMES',  'CLERK',     7698,
     TO_DATE('3-DEC-1981', 'DD-MON-YYYY'),   950, NULL, 30);
INSERT INTO EMP VALUES
     (7902, 'FORD',   'ANALYST',   7566,
     TO_DATE('3-DEC-1981', 'DD-MON-YYYY'),  3000, NULL, 20);
INSERT INTO EMP VALUES
     (7934, 'MILLER', 'CLERK',     7782,
     TO_DATE('23-JAN-1982', 'DD-MON-YYYY'), 1300, NULL, 10);

CREATE TABLE DEPT
    (DEPTNO NUMBER(2),
     DNAME VARCHAR2(14),
     LOC VARCHAR2(13) );

INSERT INTO DEPT VALUES (10, 'ACCOUNTING', 'NEW YORK');
INSERT INTO DEPT VALUES (20, 'RESEARCH',   'DALLAS');
INSERT INTO DEPT VALUES (30, 'SALES',      'CHICAGO');
INSERT INTO DEPT VALUES (40, 'OPERATIONS', 'BOSTON');

CREATE TABLE BONUS
     (ENAME VARCHAR2(10),
      JOB   VARCHAR2(9),
      SAL   NUMBER,
      COMM  NUMBER);

CREATE TABLE SALGRADE
     (GRADE NUMBER,
      LOSAL NUMBER,
      HISAL NUMBER);

INSERT INTO SALGRADE VALUES (1,  700, 1200);
INSERT INTO SALGRADE VALUES (2, 1201, 1400);
INSERT INTO SALGRADE VALUES (3, 1401, 2000);
INSERT INTO SALGRADE VALUES (4, 2001, 3000);
INSERT INTO SALGRADE VALUES (5, 3001, 9999);

CREATE TABLE DUMMY
     (DUMMY NUMBER);

INSERT INTO DUMMY VALUES (0);

COMMIT;

SET TERMOUT ON
PROMPT Demonstration table build is complete.

EXIT

Creating source and target mapings using XML transformation

Download

Informatica Pushdown Optimization Tips

Download

Revive Enterprise Architecture with Transformational SOA (Service oriented architecture) Data Integration

Download

Create Records in source by using Dynamic lookup

Download