CODEWITHSUNDEEP
sqloracle-databaseinformatica
dips saxena
dips saxena

Reputation: 1

Duplicate value removal on field by field basis in Informatica or Oracle

I have a scenario for duplicate removal on field by field basis as mentioned below which I need to implement in Informatica or Oracle. Please let me know how to do it.

Upvotes: 0

Views: 35

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269693

I recommend doing this on the reporting side, but you can do it in SQL:

select (case when row_number() over (partition by col1 order by date) = 1
             then col1
        end) as col1,
       (case when row_number() over (partition by col1, col2 order by date) = 1
             then col2
        end) as col2,
       col3
from t
order by col1, col2, date;

Upvotes: 0

pmdba
pmdba

Reputation: 7033

It looks like you want to remove duplicates in your result set from your query, not in the actual data. Grouping/breaking report values like that is a function of whatever software is producing the report, not of the database itself. The raw data would always contain all of the values, or you wouldn't be able to group it. In sqlplus, for example, this would be accomplished with the BREAK command:

Other clients would have their own way of specifying break columns in reports.

Upvotes: 0

Littlefoot
Littlefoot

Reputation: 142705

In Oracle, if you use SQL*Plus, break on columns you want (col1 and col2 in your case). Sample data in lines #1 - 10; query is a simple select everything from the table. As I said, break does the job.

Though, in my opinion, you shouldn't be doing it in SQL itself. Any decent reporting tool (Oracle Reports, Apex, ...) is capable of breaking on desired columns. You should use it.

SQL> break on col1 on col2
SQL>
SQL> with test (col1, col2, datum) as
  2    (select 'haryana',  1,  '7th feb' from dual union all
  3     select 'haryana', 12,  '8th feb' from dual union all
  4     select 'haryana', 12,  '9th feb' from dual union all
  5     select 'haryana', 11, '10th feb' from dual union all
  6     select 'pune'   ,  1, '11th feb' from dual union all
  7     select 'pune'   ,  2, '12th feb' from dual union all
  8     select 'pune'   ,  3, '13th feb' from dual union all
  9     select 'pune'   ,  3, '14th feb' from dual
 10    )
 11  select col1, col2, datum
 12  from test
 13  order by col1, col2, datum;

COL1          COL2 DATUM
------- ---------- --------
haryana          1 7th feb
                11 10th feb
                12 8th feb
                   9th feb
pune             1 11th feb
                 2 12th feb
                 3 13th feb
                   14th feb

8 rows selected.

SQL>

Upvotes: 2

Related Questions