Text only | Text with Images

COGNOiSe.com - The IBM Cognos Community

Legacy Business Intelligence => COGNOS ReportNet => Topic started by: elisha on 21 Sep 2005 05:22:35 PM

Title: Deleting duplicate rows in a table
Post by: elisha on 21 Sep 2005 05:22:35 PM
How to completely deleteÃ,  duplicate rows? while I'm working with FM ?Ã, 
for exampleÃ,  i have table like thisÃ, 

JobidÃ,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  JobnameÃ,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  commission
00001Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  managerÃ,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  5000
00002Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  assistantÃ,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã, 4000
00003Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  ClarkÃ,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  3000Ã,  Ã, 
00001Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  managerÃ,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  7000

Jobid & jobname is repeated.
more over i dint have any primary & foreign key.Ã, 

Title: Re: Deleting duplicate rows in a table
Post by: sir_jeroen on 21 Sep 2005 05:29:36 PM
what about the comission? That's not the same...
You can use grouping:

select jobid, jobname, max(commission) as Commission from tableA group by jobid, jobname

or AVG / MIN instead of max()

This should do the trick.. but be carefull..

It's all full of tricks.. especially when it's time/date related....

So, maybe you can better post the full table definition...


Title: Re: Deleting duplicate rows in a table
Post by: Merri on 21 Sep 2005 05:46:07 PM
It also depends what's causing the duplication. If the "commission" field is different, is this because of a genuine additional transaction, or it is a modification of the original transaction? If you're using SCDs, do you need to filter on the current record indicator?

As ReportNet Addict said, we need more information about your data.
Title: Re: Deleting duplicate rows in a table
Post by: elisha on 21 Sep 2005 06:22:30 PM
Thank you
Can you explain how to group Query items in FM?. 
Title: Re: Deleting duplicate rows in a table
Post by: sir_jeroen on 21 Sep 2005 06:25:09 PM
You have to use native / pass-through SQL in your database query subject.
and type in the grouped sql.
Title: Re: Deleting duplicate rows in a table
Post by: bdybldr on 13 Dec 2005 10:11:16 PM
That isn't exactly a duplicate row because commission is different.   Is that simply an oversight or are these actual number?  Which record do you want to use?  Please explain the results that you're looking for.
Title: Re: Deleting duplicate rows in a table(solved)
Post by: elisha on 28 Jul 2006 03:52:56 AM

thanks for every one
Text only | Text with Images