Q1.

Source/Input files(2 files with same structure):

File1.txt
Andrew|PRES|Addline1|NJ|USA
Samy|NPRS|Addline1|NY|USA

File2.txt
Bharti|PRES|Addline1|KAR|INDIA
Ajay|PRES|Addline1|RAJ|INDIA
Bhawna|NPRS|Addline1|TN|INDIA

Expected Output (relational table): To load the file name along with data available in flat
files.

File Name Name Type Address Line State Country

File1.txt Andrew PRES Addline1 NJ USA
File1.txt Samy NPRS Addline1 NY USA
File2.txt Bharti PRES Addline1 KAR INDIA
File2.txt Ajay PRES Addline1 RAJ INDIA
File2.txt Bhawna NPRS Addline1 TN INDIA

Q2. Load n number of records equally to 4 targets. Use a seq generator Tnx as counter------
USE PARTITION

Q3. What is the commit type if you have a transaction control transformation in the
mapping?
1. Target
2. User-defined
3. Source

Q4. Generate Dynamic Target Files based on ship year.

Source(Relational): SALES.ORDER_DETAILS
Target(Flat file): 2004.csv ,2005.csv, 2006.csv and so on..
Target File Location: D:/Informatica/DataDump/ORDERS/

Q5. Create a mapping which picks up all the files created in Q4 example and write into a
single file.

Q6. Create a mapping which generates files in much a way that each target file contains
100000 records or less

Source(Relational): SALES.ORDER_DETAILS
Target(Flat file): orders_1.csv ,orders_2.csv, orders_3.csv,orders_4.csv and so on..
Target File Location: D:/Informatica/DataDump/ORDERS/

Q7. Joiner Tnx Behaviour: Table1(Master) having 10 records and Table2(Detail) having 10
records; both tables are having 5 matching records, then how many records will the
downstream transformations get when it is configured with a join type as:
 Normal ---5
 Master Outer ---10
 Detail Outer ----10
 Full Outer ---15
Q8. Create a mapping to create tables T1, T2, T3 using a mapping in sales and target DBs.
Hint: SQL Tnx

Q9. Run the scripts placed in the path ..\SourceFiles\Scripts\ and log an audit table with
status file name and status along with errors when failed.

Q10. Create a Lookup feature using SQL Tnx(might return more than one records in most of
the cases) and load the data into table with product_name, branch_code,
opening_inventory, quantity_shipped

Source: select product_name, product_number from PRODUCT_NAME_LOOKUP where

PRODUCT_LANGUAGE=’EN’;
Lookup on INVENTORY_LEVELS using product_number to get branch_code,
opening_inventory, quantity_shipped

Q10. Create a mapping with SQL transformation to load target table with name and record
count based on the SQL: select count(*) from ~SRC11~ ~WHERE_CLAUSE~

Source File Format: Delimited using |

TA|CA=’dgg’
TB|CB=’DFH’

Q10. Create an SQL transformation which deletes record from the episodic/staging table
based on the batch reference id ie

SQL Query :
DELETE
FROM ~EPS_TABLE~
WHERE BATCH_REF_ID = (
SELECT max(BATCH_REF_ID) FROM
ETL_BATCH
);

Q11. Design a mapping to load the first record from a flat file into one table A, the last record
from a flat file into table B and the remaining records into table C?
 Use any Tnx
 Use Rank to get first and last record
Q12. Create a mapping to load from SALES.BRANCH table from to a flat file with header and
footer added
Header: ***** BEGIN Of Report *****
Footer : ***** END Of Report *****

Q13. Create a mapping to load data from the table sales.PRODUCT_FORECAST table along
with the file present in
\Source Files\PRODUCT_FORECAST2013.csv is a single pipeline.
Note: The file shares the same structure. Do not use Joiner Transformation

Q14. Create a mapping to load data from SALES.RETURNED_ITEM to TRG.RETURNED_ITEM.

When a record comes with RETURN_QUANTITY>500 and ASSIGNED_TO is null call an
unconnected stored procedure (Normal Mode) from an expression Tnx

Syntax: IIF(RETURN_QUANTITY>500 and ASSIGNED_TO is null,sp:

SP_LOAD_PRORITY_TABLE(ORDER_DETAIL_CODE))

>SP_LOAD_PRORITY_TABLE loads data into ORDER_PRORITY_TBL with ORDER_DETAIL_CODE,

ORDER_NUM, PRODUT_NAME, Actual_quantity, return_quantity.

Q16. Configure mapping to read data from a file having four delimiters (| $ @ ~)
Source Flat file: ..\SourceFiles\multiple_delimiter_file.csv

Q17. In a joiner transformation, you should specify the source with fewer rows as

1. Master source.
2. Detail source
Why?

Q18. Design a mapping to load the last 3 rows from a flat file into a target?

 Use aggregator and joiner

 Rank transformation

Q19.

id val
1 a,b,c
2 pq,m,n
3 asz,ro,liqt

Here the "val" column contains comma delimited data and has three fields in that column.
Create a workflow to split the fields in “val” column to separate rows. The output should look
like as below:

id val
1 a
1 b
1 c
2 pq
2 m
2 n
3 asz
3 ro
3 liqt

Q20. Consider the following table data as the source

id value
1 A,B,C
2 P,Q,R,S,T
3 M,N

Here the data in value column is a delimited by comma.

a. Now write a SQL query to split the delimited data in the value column into multiple
rows. The output should look like below
b. Create a mapping to split data assuming “value” can have maximum 6 delimiters

id value
1 A
1 B
1 C
2 P
2 Q
2 R
2 S
2 T
3 M
3 N

Q21. Create a simple mapping to load data from SALES database to TRG database using
Parameterize Connection Object.

1. Use header/Parameter scope [Global]

2. Use header/Parameter scope
[Folder_Name.WF:Workflow_Name.WT:Worklet_Name.ST:Session_Name]

Sample:

[WorkFolder.WF:wf_Parameterize_Src.ST:s_m_Parameterize_Src]
$DBConnection_SRC=SALES_Conn
$DBConnection_TGT=TRG_Conn
Q22. Create a mapping with source qualifier SQL Query parameterized

[Folder_Name.WF:Workflow_Name.WT:Worklet_Name.ST:Session_Name]
$DBConnection_SRC=SALES_Conn
$DBConnection_TGT=TRG_Conn
$$Source_sql= select c1,c2,c3 from t

Q23. Create a mapping to load top/last 3 records from a file

a. Use rank trnx

Q25. How to get the week day from a date

EX. the date field is 10/10/2011; how can we get output as Monday, 10th October 2011?

Q26. Develop a mapping to load all records with 5th highest quantity sold for each product
into the target table.
Source: ORDER_DETAILS

 Use Rank transformation

Q27. We have a target source table containing 3 columns: Col1, Col2 and Col3. And only 1
row in the table as follows:

Col1 Col2 Col3

a b c

The target table containing only 1 column Col. Design a mapping so that the target table
contains 3 rows as follows:

Col
a
b
c

1. Normalizer transformation
2. Using expression

Q28. Source

Col1 Col2 Col3 Col4 o/p

X A1 B1 C1 _
Y A2 B2 C2 _
Z A3 B3 C3 _

When the system day is ‘Sunday’ pass port col1 values to target; when ‘Saturday’ pass port
col4 reset for the days pass col3

Q29. Design a mapping to get the first and last event for a task in a workitem_id along the
corresponding event_date and also calculate duration in hours
Source : ADW_STG_EVENT_HISTORY
Note: Use functions in Aggregator Tnx
Q30. How to remove footer from your file ?
For example the file content looks like as below:-

someHeaderhere
col1 col2 col3 col4
data1 data2 data3 data4
data5 data6 data7 data8
data1 data2 data3 data4
data1 data2 data3 data4
footer

Q31. Create a user defined function which removes multiple spaces between the characters
and extra spaces before and after the string.
HINT: use REG_REPLACE

Employee_Name Return Value

Adam Smith Adam Smith
Greg Sanders Greg Sanders
Sam Cooper Sam Cooper

Q32. Get the desired output

HINT: REG_EXTRACT
a.

Input Output

Adam Sandler Sandler

Sanchez Sanchez

Sam [Sam followed by white space] Empty String

St John Adam John Adam

b.
Input Output

NSN:LIBS-12292423-060115111437 060115111437

NSN:4298701410513:050613105002 050613105002

Q33. The following regular expression extracts first matched number in sequence

Employee Output

No9ing 9
Test789data 789

Test Empty String

Test67ab89a 67

Q34. Create a public user defined function called PHONE_NUMBER takes a 10 digit number
as input and output a string formatted in (XXX) XXX-XXXX.

Q35. Create a procedure which accepts two dates and returns the duration in hours
If IN_DATE1>IN_DATE2 then the duration should be in negative
CREATE OR REPLACE PROCEDURE CAL_DURATION( IN_DATE1 IN DATE, IN_DATE2 IN
DATE , DURATION_HRS OUT NUMBER)

Q36. When a mapping reads and writes into a same table. How to overcome deadlock
situations?