perl script help needed

[achiganmi]

hi all...
i need to write a perl program which does the following:

it connects to the database and retrieves the rows from a table, which i am able to do.

For example, the table looks like this :
Name PID CID LID
a1 0 0 10
a1 0 0 20
a1 0 1 30
a2 0 2 40
a2 1 1 50
a3 0 1 10


so, here i need to check for duplicates and the rule is as folows:

eliminate duplicate rows which has the same CID, same PID and same name.
SO in the above example, i need to eliminate either row 1 or row 2. LID is NOT an issue here, i mean it does not matter if they are same or different.

I am storing all columns in separate arrays and then trying to sort based on columns. I just have an idea but I would like to have the perfect working code.

I will be grateful if some1 could provide me with one.
Thankyou.
Regards,
Abhilash

[numberwhun]

hi all...
i need to write a perl program which does the following:

it connects to the database and retrieves the rows from a table, which i am able to do.

For example, the table looks like this :
Name PID CID LID
a1 0 0 10
a1 0 0 20
a1 0 1 30
a2 0 2 40
a2 1 1 50
a3 0 1 10


so, here i need to check for duplicates and the rule is as folows:

eliminate duplicate rows which has the same CID, same PID and same name.
SO in the above example, i need to eliminate either row 1 or row 2. LID is NOT an issue here, i mean it does not matter if they are same or different.

I am storing all columns in separate arrays and then trying to sort based on columns. I just have an idea but I would like to have the perfect working code.

I will be grateful if some1 could provide me with one.
Thankyou.
Regards,
Abhilash

First, Welcome to TSDN!!

As for your issue, you have not posted any code so that we can see what you have tried thus far. If you could please post the code you have been working on, enclosed in the necessary [code] [/code] tags.

I understand that you would just like someone to provide you code to do what you want, but please know that this is not a code writing service. Someone may provide you snippets, but not all of the code. Being a learning forum, you are required to do a lot of the coding for your project(s).

Regards,

Jeff

[achiganmi]

hello... sorry i did not know that..

anyways here is my code...

$sql="select ed.HostIndex, ed.CPUNum, ed.GenErrCode, ed.Interpretation, sm.CorePerPhys, sm.LogPerCore, sm.PhysID, sm.CoreID, hf.TotalDownTime, (hf.Deathtime-hf.Birthtime-hf.TotalDownTime) as TotalUpTime from emmaDecode_WINtable ed, SMTinfo_WINtable sm, HostLifetime_WINtable hf where ed.HostIndex = sm.HostIndex and ed.CPUNum = sm.CPUNum and ed.HostIndex = hf.HostIndex and sm.HostIndex = hf.HostIndex and ed.GenErrCode between 2048 and 4095 limit 10;";
 
$dbh=DBI->connect($dsn,$user,$pass);
$sth=$dbh->prepare($sql);
$sth->execute;
 
sub bubble_sort;
sub swap;
 
my @HostIndexArray;
my @CPUNumArray;
my @CodeArray;
my @InterpretationArray;
my @CPPArray;
my @LPCArray;
my @PIDArray;
my @CIDArray;
my @DownTimeArray;
my @UpTimeArray;
 
while(($HostIndex, $CPUNum, $Code, $Interpretation, $CPP, $LPC, $PID, $CID, $DownTime, $UpTime) = $sth->fetchrow_array())
{
       $numrows=$sth->rows;
       push(@HostIndexArray, $HostIndex);
       push(@CPUNumArray, $CPUNum);
       push(@CodeArray, $Code);
       push(@InterpretationArray, $Interpretation);
       push(@CPPArray, $CPP);
       push(@LPCArray, $LPC);
       push(@PIDArray, $PID);
       push(@CIDArray, $CID);
       push(@DownTimeArray, $DownTime);
       push(@UpTimeArray, $UpTime);
}
 
for($a1 = 0; $a1 <= $numrows; $a1++)
        {
         print "$HostIndexArray[$a1] \t $CPUNumArray[$a1] \t $CodeArray[$a1] \t $InterpretationArray[$a1] \t $CPPArray[$a1] \t $LPCArray[$a1] \t $PIDArray[$a1] \t $CIDArray[$a1] \t $DownTimeArray[$a1] \t $UpTimeArray[$a1] \n";
        }
 
 
sub swap
{
  my($index1, $index2) = @_;
 
  ($HostIndexArray[$index1], $HostIndexArray[$index2]) = ($HostIndexArray[$index2], $HostIndexArray[$index1]);
  ($CPUNumArray[$index1], $CPUNumArray[$index2]) = ($CPUNumArray[$index2], $CPUNumArray[$index1]);
  ($CodeArray[$index1], $CodeArray[$index2]) = ($CodeArray[$index2], $CodeArray[$index1]);
  ($InterpretationArray[$index1], $InterpretationArray[$index2]) = ($InterpretationArray[$index2], $InterpretationArray[$index1]);
  ($CPPArray[$index1], $CPPArray[$index2]) = ($CPPArray[$index2], $CPPArray[$index1]);
  ($LPCArray[$index1], $LPCArray[$index2]) = ($LPCArray[$index2], $LPCArray[$index1]);
  ($PIDArray[$index1], $PIDArray[$index2]) = ($PIDArray[$index2], $PIDArray[$index1]);
  ($CIDArray[$index1], $CIDArray[$index2]) = ($CIDArray[$index2], $CIDArray[$index1]);
  ($DownTimeArray[$index1], $DownTimeArray[$index2]) = ($DownTimeArray[$index2], $DownTimeArray[$index1]);
  ($UpTimeArray[$index1], $UpTimeArray[$index2]) = ($UpTimeArray[$index2], $UpTimeArray[$index1]);
}
 
sub bubble_sort
{
      for ($i=0; $i < $numrows; ++$i)
        {
                for ($j=0; $j < $numrows-$i; ++$j)
                 {
                        if($PIDArray[$j] > $PIDArray[$j+1])
                           {
                                swap($j, $j+1);
                           }
                 }
        }
}
 
bubble_sort();
 
for($a2 = 0; $a2 <= $numrows; $a2++)
        {
         print "$HostIndexArray[$a2] \t $CPUNumArray[$a2] \t $CodeArray[$a2] \t $InterpretationArray[$a2] \t $CPPArray[$a2] \t $LPCArray[$a2] \t $PIDArray[$a2] \t $CIDArray[$a2] \t $DownTimeArray[$a2] \t $UpTimeArray[$a2] \n";
        }
 
 
for ($i=0; $i < $numrows ; $i++)
 {
    my @temparr;
    print "\n \n Value of i = $i.......$PIDArray[$i]......... $CIDArray[$i]";
    print "\n Value of (i+1) = ($i+1) ....... $PIDArray[$i+1]....... $CIDArray[$i+1]";
    if(($PIDArray[$i] == $PIDArray[$i+1]) && ($CIDArray[$i] == $CIDArray[$i+1]))
          {
        print " \n Going... ";
        $temparr[$i] =  0;
      }
    print "\n Temparr = $temparr[$i]";
 }
 
 

Here , the I am sorting based only on the PID array, but not able to sort the PID based sorted array based on the CID array.

Somehow, I am overwriting it if I am consecutively sorting the arrays in the fol loop.

I need to remove duplicates , and so I would like to do this.

Any other better idea... please let me know.
thank you.
Regards,
Abhilash

[eWish]

Do you administer the database and can you create tables? If, so then I would recommend that you normalize tables. With the proper use of indexes, primary keys you could eliminate duplicates before they are inserted into the database.

An Introduction to Database Normalization

[achiganmi]

Hello...
No I am not administering the database.....
i have no other options but do the sorting thing... could you please help me with it??
regards,
abhilash