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

How to use Environment variable's in datastage?(use of process)

1 Answers   CSC,

explain about completely flow of sequencers technicaly,without using example??explain about lookup,nullhandling?

0 Answers   CGI,

Have you used Unstructured data?

0 Answers   CTS,

What is configuration your file structure 2)I have two databases both are Oracle while loading data from source to target the job takes 30 min but I want to load less time how?

1 Answers   Hexaware,

how can i abort the job if i get more than 100 errors in job log?

6 Answers   Accenture,

I have a source table with column name CITY having 100 records, I want target table with column name start with 'A' and 'B',remaining columns as reject outputs. how can achieve this by data stage?please help me?????

5 Answers  

how to sort two columns in single job in datastage.

1 Answers   TCS,

how can we generate row values like 1 1 1 2 2 2 3 3 3 using col generator plz let me know

2 Answers  

Hi friends,Two input files, wants to validate only if the reference data has '0' otherwise no validation should be done..how to do this??

1 Answers   IBM,

What are the some differences between 7.x and 8.x version of datastage?

0 Answers  

What are some prerequisites for datastage?

0 Answers  

Hi all, can u explain header and trailer records in file? If a file has header and trailer /master-detail records how will you read it in datastage?

3 Answers