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

hi, how would i run job1 then job 3 , then job2 in a sequence of job1 ,job2,job3. Thanks sunitha

6 Answers   CTS,

which is the most complex job in your career? Please tell me answer?

2 Answers   IBM, Wipro,

what is Audit table?Have u use audit table in ur project?

3 Answers   Accenture, Wipro,

I am having the 2 source files A and B and I want to get the output as, the data which is in file A and which doesn't in file B to a target 1 and which is in file B and which doesn't in file A to a target 2?

3 Answers  

How to write a expression to display the first letter in Caps in each word using transformer stage ? Please let me know ASAP Thanks in advance...

0 Answers   Alpharithm Technologies,

how to get the unique records on multiple columns by using sequential file stage only

3 Answers  

Name the third party tools that can be used in datastage?

0 Answers  

How can i approach to write datastage 7.5 Certification? and how much they will charge for examination .What exactly should i do? Can anyone guide me plz?

2 Answers  

1.which index is follows the dimensions tables?why? 2.what is the use of trigger in job sequence? 3.what is the mean of optimization? 4.what is the job control?when we use it? what is difference bet batch and sequencer? 6.seq--->seq,seq--->copy--->seq which one is best and efficient?

1 Answers  

Hi Gus, Can u pls tell me How can u Call the Shell Scripting/Unix Commands in Job Sequence?

3 Answers   IBM,

what is the difference between the join and look up explin me one exmple

3 Answers  

how to export or import the jobs in .ISX file

0 Answers