Answer / sivaprakasam

select * from table1 where id in (select id from table1
group by id having COUNT(id) > 1)

Answer / prathapreddy

select col1,count(*) from <table name> group by col1 having
count(*)>1;

Answer / sudheer

the below query is to get only duplicate records
i.e..,
10
10
20
20

select * from sno
where sno in (select sno from ex
group by sno
having count(sno)>1
)

and the above query is to get only
10
20

Answer / subhash

From above all, these 3 are the correct ways using SQL:
A.
select * from emp as p where rowid<(select max(rowid) from
emp s where p.deptno=s.deptno);
B.
select * from sno
where sno in (select sno from emp
group by sno
having count(sno)>1
)
C.
1. SELECT A.COL1 FROM TABLE1 A

2. ( SELECT COL1, COUNT(COL1)FROM TABLE1
GROUP BY (COL1)
HAVING COUNT(COL1)=1)B

WHERE

3. A.COL1<>B.COL1

RESULT : 1. 10 20 30 40 10 20 50
2. 30 40 50
3. 10 10 20 20
And Using DataStage:
A.
Take source and copy,aggregate,JOIN,FILTER
1) give one copy output link to aggr, other to JOIN

2)In arggr perform count rows,
col1 COUNT
10 2
20 2
30 1
40 1
50 1

3) JOIN the two links(link1 from copy, link2 from AGG) and
use LEFT OUTER JOIN
col1 COUNT
10 2
20 2
30 1
40 1
10 2
20 2
50 1

4)And in FILTER, give constrains as count=1 to TARGET1 and
count>1 to TARGET2. in the TARGET2, u will get the desired
output.

Answer / d.bharath

Take source and copy,aggregate,transformer and lookup,
1) give one copy output link to aggr,

2)In arggr perform count rows,

3)And in transformer check constrains count>1,count>2

4)And lookup the result of count>2 with the second output
link of copy stage.the you will get the all repeated rows
into on one output dataset.

Answer / ramesh

SQL:

1. SELECT A.COL1 FROM TABLE1 A

2. ( SELECT COL1, COUNT(COL1)FROM TABLE1
GROUP BY (COL1)
HAVING COUNT(COL1)=1)B

WHERE

3. A.COL1<>B.COL1

RESULT : 1. 10 20 30 40 10 20 50
2. 30 40 50
3. 10 10 20 20

Answer / vamsi

HI,
ANY ONE TELL ME HO WE CAN DONE THIS IN DATASTAGE?

Answer / mcssrinivas009

select RANK , D *from(select roumun RANK,E. *from E) D where
RANKIN 1,2,5,6;
using this query we getting from our requirement

Answer / sivakrishna

select * from emp as p where rowid<(select max(rowid) from
emp s where p.deptno=s.deptno);

Answer / geetha

To achive the same in datastage we can use aggregator stage.

Check the below link
http://mydatastage-notes.blogspot.in/p/aggregatorstage.html

What is the difference between orabulk and bcp stages?

0 Answers  

Hi All , in PX Job I have passed 4 Parameters and when i run the same job in sequence i dont want to use those parameters , is this possible if yes then how

0 Answers   TCS,

What is the difference between datastage and datastage tx?

0 Answers  

1.How do u handle NULL in sequential stage. 2.Difference between switch stage and filter stage.

4 Answers  

how do you pass parameters in a script?

1 Answers   L&T,

What are the various kinds of the hash file?

0 Answers  

what is operator combinality in datastage?

1 Answers   TCS,

A flatfile contains 200 records.I want to load first 50 records at first time running the job,second 50 records at second time running and so on,how u can develop the job?pls give the steps?pls pls

7 Answers   TCS,

What are the various kinds of containers available in datastage?

0 Answers  

What is the Environment Variable need to Set to TRIM in Project Level?(In transfermer, we TRIM function but I need to impliment this project level using Environment variable)

0 Answers   IBM,

How can we perform 2nd time extraction of client database without accepting the data which is already loaded in first time extraction?

1 Answers   Infosys, TCS,

3) Sequential file contains data like Empno ename sal 111 abc 2000 Trgt file: Trgt1----111 Trgt2----abc Trgt3---2000

2 Answers