how do u fnd the duplicate rows and how to delete the
duplicate rows?
Answer Posted / suraj kedia
Hi Sankar...
The methods as told by u r correct but i guess sree has
asked about finding duplicate records and the way to delete
them in oracle not in informatica.
So, the query to find out the duplicate records in a given
table (e.g. emp table) is as below:
select * from emp where empno in (select empno from emp
group by empno where count(*) >1);
Explanation:
============
See, in EMP table there are total 14 records out of which
only empno is unique,rest all fields can be repeated, so the
empno which are repeated more than once are duplicate
records & the above query shows the same.
Query to delete duplicate records;
delete from emp where rowid not in (select max(rowid) from
emp group by empno);
OR
delete from emp where rowid not in (select min(rowid) from
emp group by empno);
OR
delete from emp e1 where rowid < (select max(rowid) from emp
e2 where e1.empno=e2.empno);
OR
delete from emp e1 where rowid > (select min(rowid) from emp
e2 where e1.empno=e2.empno);
Is This Answer Correct ? | 21 Yes | 3 No |
Post New Answer View All Answers
Why do we need informatica?
What is depict expression change?
explain any diffcult scenario that u have faced in your experience... or explain any complex maping u have developed?
Difference between Target-based loading and constraint-based loading?
Define joiner transformation?
What happens to map if we alter the datatypes between source and its corresponding source qualifier?
Explain about cumulative Sum or moving sum?
How many ways are there to create ports?
How to extract sap data using informatica? What is abap? What are idocs?
What is informatica metadata and where is it stored?
Can we override a native sql query within informatica?
design of staging area?
Explain the types of lookup transformation?
What does “tail –f” command do and what is its use as an Informatica admin.
Hello , I am unable to work with SQL transformation at least. Where do i need to give connection for sql transformaton ? At session level there is no property . I have created a SQL Transformation and chosen query mode. But do i need to pass connection information to it ? I don't know where do i need to write a query ? I have written a query in file and that file path i gave in the properties of SQL Transformation. But it is not working. Could any one of you please let know how can i work with SQL Transformation? Advance Thanks.