Hi All,
Thanks for all your comments. I think I have more questions than I initially had. Which is good ! Now I understand the XY problem. I have
a database which has 12 tables. The keys are column names from the excel sheet.
This is my primary table and the rest in one to many relationship with this table.
I have one value for the primary column in my LI table(COLUMN NAME LI) and can have multiple values for different columns in the same or different table.
my $li_insert = "insert into LI values(LI_SEQ.nextval,?,?,?,?,?,?,?,?,?,sysdate,?,?,?,?)";
my $mechanism_insert = "insert into mechanism (TAGET,ACTIONTYPE,ID,LI_ID) values(?,?,MECH_SEQ.nextval,LI_SEQ.currval)";
Here one LI_ID can have multiple action_type or TAGET.
So I want my script to be able to insert
5,2,1,12
6,1,2,12.
The data would be like this:
{
'taget '=>
5,6,
'Action_type' =>
2,1
}
in the excel sheet it actually comes as joined with a pipe operator I split them to have an array in a subroutine. How could I achieve this type of insertion if I keep them joined?
Hope this is clear now!Let me know if you need any more details.
# to get each hash from an array of hash
foreach my $results(@$hash_of_excel){
my $specificresults = GroupResults($results);
}
sub GroupResults{
#to getonly the relevant columns(excel) for each table in the database
+.
#%details is a global hash tha holds different tables and their corres
+ponding column
my( $AllRecords )= @_;
my %specificResults;
for my $info (keys %details){
$specificResults{$info}{$_} = $AllRecords->{$_} for @{$details{$in
+fo};
}
}
return \%specificResults;
}
sub SomethingUnPipeModifyRecord {
#convert the joined values to array of values
my( $rec ) = @_;
for my $val( values %$rec ){
my $newval = [ split /\|/, $val ];
$val = $newval;
}
return $rec;
}
sub insertLI{
my ($li_results )= @_;
my $li_array_values = SomethingUnPipeModifyRecord($li_results);
}
sub insertMech{
my ($mech_results ) = @_;
my $mech_array_values = SomethingUnPipeModifyRecord($mech_results);