SQL Scenario - 1

You have a table like below :

Name Age Gender
Atul         11 F
Ram 10 F
Disha  6 M
Pavan  8 F
Mani         10 M
Lovely 10 M
Rani    9 M

DataStage Scenario - Problem21

Input Table 1

Col1	Col2
A	11
B	23
C
D	45

Create Fixed Width Data in DataStage

In day to day life of DataStage, you are facing lot of business scenario and technical scenario. Today, we are going to discuss how to generate Fixed Width data from variable length data in datastage.


Input :
23
3345
546576
535
80
2342


Output :
0000023
0003345
0546576
0000535
0000080
0002342

DataStage Scenario - Design10 - job1

DataStage Scenario Problem -->  DataStage Scenario - Problem10 

Solution Design :

a) Job Design :  

   Below is the design which can achieve the output as we needed. In this design, we are reading the data from flat file, generating a dummy column in column generator stage, doing aggregate on that and sending the output to seq file.

DataStage Scenario - Problem20

Goal : Reverse the Column

Input is like this :

i/p
col1,col2
101,a
102,b
103,c
104,d

DataStage Scenario - Design8 - job1

DataStage Scenario Problem -->  DataStage Scenario - Problem8

Solution Design :

a) Job Design : 
Below is the design which can achieve the output as we needed. Here, we are reading 2 seq file as a input, then data is passing through a Join and Filter stage to achieve the output.

DataStage Scenario - Design7- job1

DataStage Scenario Problem -->  DataStage Scenario - Problem7

Solution Design :

a) Job Design :  Below is the design which can achieve the output as we needed. Here, we are reading seq file as a input, then data is passing through a Transformer and Peek stage to achieve the output.

DataStage Scenario - Design6 - job1

DataStage Scenario Problem -->  DataStage Scenario - Problem6

  

Solution Design :

a) Job Design :

Below is the design which can achieve the output as we needed. Here, we are reading seq file as a input, then data is passing through a Transformer stage to achieve the output.

DataStage Scenario - Problem19

Goal : Reverse duplicate Removal

Input :
source | destination | distance
city1|city2|500
city2|city1|500
city3|city4|500
city4|city3|500 

DataStage Scenario - Problem18

Goal : Get the y'day date as a column and change the column format

Input :

Country|Currency|ValidFrom|ExRate
CNH|USD|1-Jan-12|0.11
CNH|USD|5-Jan-12|0.8
CNH|USD|10-Jan-12|0.15
CNH|USD|15-Jan-12|0.14
CNH|USD|20-Jan-12|0.16

DataStage Scenario - Problem17

Goal : Count the data in each column

Input :

col1	col2	col3
a	{NULL}	b
f	k	{NULL}
h	{NULL}	n
i	d	{NULL}
{NULL}	s	{NULL}
g	u	m
l	x	o
m	{NULL}	{NULL}
c	d	z

DataStage Scenario - Design2 - job3

DataStage Scenario Problem -->  DataStage Scenario - Problem2

Solution Design :


a) Job Design :

DataStage Scenario - Design2 - job2

DataStage Scenario Problem -->  DataStage Scenario - Problem2

Solution Design :


a) Job Design :
In job design, we are using Copy, Aggregator, Filter and Join stage to get the output.

DataStage Scenario - Design 2 - job1

 DataStage Scenario Problem -->  DataStage Scenario - Problem2

Solution Design :


a) Job Design :

Below is the design which can achieve the output as we needed. Here, we are reading seq file as a input, then data is passing through Aggregator and Filter stage to achieve the output.
 

DataStage Scenario - Problem16

1st I/P file 
BRAND, MT, RE 
OV ,1 ,RE 
VG ,2 ,RE 
WU ,3, RE 


2nd I/P file 
BRAND, MT, CX 
OV ,4, CX 
VG ,5, CX 
WU ,6, CX 

DataStage Scenario - Problem15

Goal : Get the Previous column value in current value 

Input file : 

Sq, No 
1,1000
2,2200
3,3030
4,5600

DataStage Scenario - Problem14

Goal : get below outputs

Input : 
dept, emp
----------------------------
20,            R
10,            A
10,            D
20,            P
10,            B
10,            C
20,            Q
20,            S 

DataStage Scenario - Problem13

Goal : Repeat the input as it is in input

Repeat the input in output, Here what we have to do is repeat the same row no of times in output file. Suppose if 2 is there then repeat 2 two times, 4 four times in output files.


Input :
2
3
4
1

DataStage Scenario - Problem12

Goal : Add a comment line with each row of input file


Input1 :

Name, Dept, Salary
CHRISTINE,A00,152750.00
MICHAEL,B01,94250.00
SALLY,C01,98250.00
JOHN,E01,80175.00
IRVING,D11,72250.00
EVA,D21,96170.00
EILEEN,E11,89750.00
THEODORE,E21,86150.00

DataStage Scenario - Problem11

Which stages are needed to achieve below output ??

Input :

col1,col2
1,1
2,rajesh
3,15000
4,2
5,suresh
6,16000
7,3
8,veeru
9,17000