i/p o/p1 o/p2
1 1 4
1 1 5
1 1 6
2 2
2 2
2 2
3
3
4
5
6
how to populates i/p rows into o/p1&o/p2 using datastage
stages?and also the same scenario using sql?
Answer Posted / radhakant kumar
1)by sql say the column name is Id then
a)(for duplicate)
select a.id
from table_name a
left outer join(select id,count(*)as no1
from table_name
group by id
)b
on b.id=a.id
where b.no1>1
order by a.id
it will display 1 1 1 2 2 3 3
b)change the condition where b.no1=1 then it will display
the remainings without duplicates..
2) By DataStage
Step By Step....
a)after source file take Copy stage and draw two
output links
b)one will go to Aggregate stage and take count of
rows by group by Field(id)
it will display like.
ID Count(no)
1 3
2 3
3 2
4 1
5 1
6 1
c)and then use lookup stage main stream will be second link
of copy and reference will be output of Aggregate stage
d)look up key will be ID and fetch count(no) to target
it will display
ID Count(no)
1 3
1 3
1 3
2 3
2 3
2 3
3 2
3 2
4 1
5 1
e)and after lookup stage take transformer or filter stage
by taking two output links and put condition
a)for duplicates count(no)>1
b)for non duplicates count(no)=1
Finally u will get your desired output..
6
Is This Answer Correct ? | 13 Yes | 1 No |
Post New Answer View All Answers
In work load management there are three options of Low priority, Medium priority and High Priority Jobs which can be used for resource management. why this feature is developed when there is already jobs prescheduled by scheduler or autosys. what will be the use of workload management then?
Where do you see different stages in the designer?
EXPLAIN SCD
What are iconv and oconv?
I/p : F1 table have A,B,C,D,E and F2 table having C,E,V i need output 0utput1: ABD output2: V any one suggest me this
What is ds designer?
Difference between IBM DATA STAGE8.5 and DATA STAGE9.1 ?
how to achieve this output ? Two Input columns(ID & Name) - ID | Name 1 | Jack 1 | Kara In output there should be only 1 column which will be populated as - 1,Jack 1,Kara
What is datastage engine?
for example You have One Table with 4 Columns (Mgr ID, Department ID, Salary, Employee ID). Can you find out the Average Salary and Number of Employee present per Department and Mgr
how can we create rank using datastage?what is the meaning of rank?
Distinguish between informatica & datastage. Which one would you choose and why?
How do you generate sequence number in datastage?
What is orabulk stage?
what is repositery?