Group Discounts available for 3+ students and Corporate Clients

Horizontal Sorting in Data Stage

Horizontal Combining

Combining primary rows with Secondary rows based on key column values

The stage that perform  Horizontal combining are

  • Join
  • Look – up
  • Merge

These Stage differ with each other w. r. t

  1. input requirements
  2. Treatment of unmatched records
  3. Memory usage


Description join lookup Merge
Input Names  Screenshot_38
  1. Primary /i/p
  2. Reference lookup

S1 – -> lookup

Types of joins Inner leftouter,right outer, full outer Inner, leftouter Inner left outer
n-i/p2- i/p1-o/p

No reject

n-i/p2- i/p1-o/p




key column Name requirements Key column Name should be SAME
In general- optional

It should be SAME in look up file set

Key column name should in SAME
inner join type
p.k column values should have matching with “ALL” secondary records


p/k column values should have matching with “all” secondary records.


p.k columns values should have matching with “Any” secondary records.


  1. i/p requirements sorting
  1. primary sorting

secondary sorting

  1. mandatory


  1. optional


  1. mandatory


  1. i/p requirements w.r.t de- duplication (remaining duplicates)
  1. primary


  1. no need to de- duplicate- ok


  1. ok


  1. warnings(make sure that primary should be de- duplicate)


  1. treatment of un matched records

a.  primary

b.  secondary



  1. drop/target/reject

only drop

  1. drop/target


memory usage light heavy light



Cross  verification of primary records with Secondary records.

It is a processing Stage which performs Horizontal combining

In Data stage we have 4 types of look – ups


Normal lookup

Look up at memory, that is all the Secondary records are copied  to the memory and primary records are cross verified with Secondary records at memory.

Sparse look up

Look – up at Source , that is each primary record is send to the Source for cross verification.

look up, failure condition :-

  1. Continue 7 left outer joins
  2. Drop 7inner join
  3. fail
  4. Reject


4  types of joins can be performed


E no E name Dept no
11 a 10
22 b 20
33 c 30



Dept No D Name loc
10 Account NY
20 Search OL
40 Sales CH


Inner Join 

T1 n T2 = {10, 20}

Matched records from primary and Secondary Tables .

Left outer join

T1 n (t1 u t2)  = {10, 20, 30}

Matched records from primary and Secondary Tables and unmatched   records from primary.

Right outer join 

T2 n (t1 u t2)  = {10, 20, 40}

Matched records from primary and Secondary Tables and unmatched  records from Secondary.

Full outer Join 

T1 u T2 = {10,20,30, 40}

7Matched and un matched records from primary and secondary table


Job :- look up





Eid Ename State Status
11 SUMAN AP 1001
22 MOON TN 1000
33 SumaN AP 1111
44 Moon STAR NN 1011
55 SUMAN AP 1101
66 MooN JK 1001
77 MoonSUN AP 1011
88 MooN MP 1111



State State desc
Ap Andhra Pradesh
TN TamilNadu
JK Jammu Kashmir
DL Delhi
KK Kenya kumari


Sequential file 1 

Load primary.txt 7 columns7 select   state

 Sequential file  0 

Load primary.txt 7columns7 select   state


Look –up 

Drag key column value “State” from primary to ref   7In ref, for State, Set key = case less 7(that is case sensitive can look -up)


Key expression Range column



Derivation Column Name
Primary – EID EID
Primary- Ename EName
Ref.state State-desc
Primary- status Status


Key expression Key types Column name


Click on yellow link on the TOP  7Constraints

Set look up failure = Drop   (or) Continue  (or) Reject 7Needs are reject link


  1. (a) job :- Normal look –u p :-



Interested in mastering DataStage Training?
Enroll now for FREE demo on DataStage Training.

Dept  (4 rows)    [Secondary records are copied to memory, and primary are cross verified with Secondary at memory so, 4 rows]


oracle  0

Load dept 7 Set look up Type = Normal

oracle  1

Load EMP

look  up

Map key Column Value “Dept no”7Drag and drop required columns7Select continue

7Compile and RUN


(b )Job :- Sparse look up

7 when we need to use Sparse ?


  1. When memory is insufficient
  2. When primary records are relative less than Secondary records




  • In Sparse :- (conditions apply)
  1. If ref is data base, then it Supports only 2 inputs .



Dept  (14 rows)  [Each primary record is send to the Source that is dept for cross verification So 14 rows]

oracle 0

Load dept 71st look – up Type = Sparse




Job:- Sparse look up Sequential file

If Ref file is Sequential file, then we have an Stage called “Look up file set” 7 extension is .lfs




Sequential file 0   :-  load ref file

Sequential file 1 :- load primary file

Look up 

Map key column value “State”7Drag and drop required columnsàSet look – up failure = continue7Compile and RUN



Look up file set

Look up keys7Key = State

Keep = false


look up file set  = D: /shilpa/new sql look up.lfs

Look up Range

Range look up = No

Options Allow Duplicates = false

  • Compile and RUN

Look up file set 

  1. It creates indexing on that particular key   (key column value)
  2. In look – up file such same key column Name Should be there]
  • Copy the look up first set path
  1. Now Delete ref Sequential file in (a)


7Paste the lookup file set in the place of ref SEQ file is   (a)

7In the lookup file  set  à paste the file name here

7there is no need to map in the look up first




E id E name State Status State – dense
11 SUMAN AP 1001 Andhra Pradesh
22 MOON TN 1000 Tamilnadu


job :- Database Sparse look up


In Data base , we can use lookup file set if we have 2 ref Data bases




Range look up:-

  1. job :- Grade based on Salary 


Oracle enterprise 1

Load EMP table 7 convert Sal to integer in EMP table

Oracle enterprise 1 

Load Sal grade table  7Convert Grade, lo Sal, HSal to integer in Sal grade table




EMP. Sal >= Sal grade . Lo Sal And

EMP. Sal <= Sal grade . H Sal  7Drag and Drop required columns7click on constraints7Select Drop/Continue

Data Set

PropertiesàFile7Browse  for file 7Range look up .ds

Compile and RUN

For indepth understanding of DataStage click on

“At TekSlate, we are trying to create high quality tutorials and articles, if you think any information is incorrect or want to add anything to the article, please feel free to get in touch with us at, we will update the article in 24 hours.”

0 Responses on Horizontal Sorting in Data Stage"

Leave a Message

Your email address will not be published. Required fields are marked *


Please Enter Your Details and Query.
Three + 6