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?
Answers were Sorted based on User's Feedback
Answer / 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 |
Answer / kalpana_241871
SQL>
SELECT COL1 FROM TAB1 GROUP BY COL1 HAVING COUNT(*) > 1 ;
O/P 1 :
1
1
1
2
2
2
3
3
SQL>
SELECT COL1 FROM TAB1 GROUP BY COL1 HAVING COUNT(*) = 1 ;
O/P 2 :
4
5
6
Is This Answer Correct ? | 11 Yes | 2 No |
Answer / kavitha
sequentialfile-----copy-----head and tail(in that we have options like no of rows=6 for head and n..o..r=4 for tail)then connect two data sets to that head and tail u will get
Is This Answer Correct ? | 1 Yes | 0 No |
Answer / rvanaja.etldeveloper@gmail.com
just read the data in sequential file
and take aggregator
group=cid
aggregation type=count rows
count output column=countno
then take filter from filter you drag 2 links for two outputs
in filter write conditions like bellow and give link order
where clause=countno>1
and
where clause=countno=1
map in output links
seqfile-->aggregator-->filter-->2 torgets
Is This Answer Correct ? | 0 Yes | 0 No |
Answer / vijaya
Hi
first sort the input date then use the transformer stage by
using stage variables you can use the logic like check the
first record then check the second record if both are equal
send the output to o/p1 or else send to o/p2....
I hope i made u understand the logic
Is This Answer Correct ? | 2 Yes | 11 No |
Answer / srinivas
first sort the data,
then
take two stage variable
if (sv1=sv2) then ds.link1 else ds.link2
Is This Answer Correct ? | 1 Yes | 13 No |
DataStage Scenario based Interview Questions
col1 123 abc 234 def jkl 768 opq 567 789 but i want two targetss target1 contains only numeric values and target2 contains only alphabet values like trg1 123 234 768 567 789 trg2 abc def jkl opq
What is a ds designer?
How do u call shellscript/Batch file from DS?
i have the source from Uk,north america how can i pass the data two tables based on the locations
How do you find the number of rows in a sequential file?
What are transforms and what is the differenece between routines and transforms?
Hi, I have 3 records, 1 Srini 1 vasa 2 reddy now i want o/p like this 1 Srinivasa 2 reddy could u please help in this Thanks in advance.
what is the main difference between sorragate key n primary key in one word
What is the project in datastage?
What are the differences between datastage and informatica?
create a job to get the previous row salary for the current row.if there is no previous row exists for the current row,then the previous row salary should be displayed as null? empid salary previoussalary 10 1000 null 20 2000 1000 30 3000 2000 40 4000 3000