Dear Perl DBI users
I have two tables: currency_acc and amount_type. I would like to
extract Names from the first table who have SGD currency and keep as a
query for the second table
Table 1 currency_acc
+---------------------------------------
ID Name currency +
+---------------------------------------
23 RAM SGD
32 GOP GBP
45 RAN SGD
67 KRI USD
78 YUR SGD
+------------------------------------
Table 2 amount_type
+-------------------------------------------------
ACC RAN KRI YUR RAM +
+------------------------------------------------
SAV 675 890 760 234
CUR 345 123 567 120
NRE 123 256 897 145
NRO 787 978 545 678
+-------------------------------------------------
Task 1: Select Name from currency_acc where currency = 'SGD'
Task 2: Extract the amount_type information only for those guys who
have SGD currency
Ideal output:
+-----------------------------------------
ACC RAN YUR RAM +
+-----------------------------------------
SAV 675 760 234
CUR 345 567 120
NRE 123 897 145
NRO 787 545 678
+-----------------------------------------
I tried the following perl script, which didn't return the ideal
output.
$dbh = DBI->connect($databaseName, $databaseUser, $databasePw) || die
"Connect failed: $DBI::errstr ";
$sth = $dbh->prepare("SELECT Name FROM currency_acc WHERE
currency='SGD'"); #select the name from first table1
$sth->execute();
my $i=0;
$count=0;
while (@names = $sth->fetchrow_array()) {
$count += ($names[0] =~ /^[A-Z]/i); #Counting howmany names
need to be queried from the 2nd table
for ($i=0; $i<$count; $i++) {
$sth2 = $dbh->prepare("SELECT ACC, $names[$i], $result[$i++]
FROM amount_type"); # Keeping the names as header
$sth2->execute();
while(@new_result = $sth2->fetchrow_array()) {
push @new_result2, $new_result[0];
}
}
}
print "@new_result2 \n";
$sth->finish();
$sth2->finish();
Thanks for your help
Eddie
