Function to return the next number

[Rajey]

I need to insert a identifier number along with city records into a database table ( data is read from a file and inserted into a table using perl) such that certail records that belong to same city get the same number ...for e,g if the records are all for city "Boston" then they all should have number 1 and this number one is inserted into a table ...for if there are five records for Boston then number one is inserted five time for each row of Boston....if records for New York appear only once then it shoud get a new number lets say 2 and should be inserted just once with identifier number as 2 ..and so on...( it is assumed that all same city records appear together in the file which is read...)

I do not know how can in perl if we can write one function which on calling should give me a new number statring from number 1 (for the very first time the function is called ) then on the next call it should return 2 etc....I am planning to call that function only when I see the city is changed to a new one.....

I hope there is a easy way out....

 

[mikedaruke]

Well I think you should make the database do the work.

If you are inserting 'Boston', check the database to see what number Boston is, then assign that number to a variable and put it in your insert statement for Boston.

If the city 'Boston' doesn't have a number then check to see what the last number in the database is, add 1 to that then assign that number to a variable and put it in your insert statement for Boston

 

[Rajey]

We do not have the option to have this build in the DB so we are looking for options in perl

 

[cyan01]

I wrote a piece of code to do this kind of job.

Assume a source file named "noIndex.txt" is like this (tab delimited):

boston  aaa bbb
New York  111 222
boston  c444  e5555
boston  d444  x5555
New York  4444  55555
Washington  zzzz  yyyy
boston  r444  r5555
Washington  zzzz111 yyyy2222

A sample code is like this:

#! /usr/bin/perl

print "$0..testing...\n";
my $srceFile = "noIndex.txt";
my $destFile = "indexed.txt";
open(FH, "$srceFile") || die "$!\n";
my @buf = <FH>;
close(FH);
my %index;
my $i = 1;
open(WT, ">$destFile") || die "$!\n";
foreach my $line (@buf)
{
  my @tmp = split(/\t/, $line);
  if(!defined($index{$tmp[0]}))
  {
    $index{$tmp[0]} = $i;
    $i++;
  }
  print WT "$index{$tmp[0]}\t$line";
  print "$index{$tmp[0]}, $line";
}
close(WT);

And the output file is like this (tab delimited):

1 boston  aaa bbb
2 New York  111 222
1 boston  c444  e5555
1 boston  d444  x5555
2 New York  4444  55555
3 Washington  zzzz  yyyy
1 boston  r444  r5555
3 Washington  zzzz111 yyyy2222

 

[KevinADC]

it should be possible but there is no generic answer that will make it fit within the structure of your existing program. Use a hash to store each city name as you use it, use the exists() function to see if you have already used that city name. Increment a number and assign the value of that number to the hash keys as necessary.

 

[TrojanWarBlade]

Am I the only one here that thinks that this sounds like a school assignment?



Trojan.

 

[kordaff]

After a while, they all sound like school assignments =)

 

[KevinADC]

cyan01 has posted a number of threads and I didn't get the impression the other ones were class work assignments (at least not that I remember). I didn't get the impression that this question was either, but I reserve the right to be wrong.

 

[Rajey]

cyan01 Thanks !

Others : Question was not a assignment....

 

[KevinADC]

Actually, I was wrong, Rajey started this thread, not cyan01. But Rajey says it's not an assingment.

 

[stevexff]

I'm not convinced.

If you follow mikedaruke's suggestion and let the database do the work, you[ol][li]don't have to sort the records[/li][li]don't have to keep track of the last record you read (your other post)[/li][li]you can guarantee referential integrity[/li][li]you have a lookup you can use to determine that 1 means 'Boston' when you want to read the rows from the database later[/li][li]you can use constraints to prevent rows being inserted with invalid city keys[/li][li]and you can use the autonumber or sequence facilities in the database so you don't even have to generate the key-numbers in the first place[/li][/ol]If this really isn't a school assignment, I can't believe your DBA actually thinks it's better to allow you to make up city codes at random and insert them into the table rather than spend five minutes creating a small reference table for you.

Steve

[small]"Every program can be reduced by one instruction, and every program has at least one bug. Therefore, any program can be reduced to one instruction which doesn't work." (Object:erlDesignPatterns)[/small]