[prev in list] [next in list] [prev in thread] [next in thread] 

List:       dbi-users
Subject:    Re: Extracting values from one table and query the values as a query
From:       207.171.7.86
Date:       2011-09-01 2:24:23
Message-ID: c0b2697e-4757-40bb-8194-801df9acc8fb () s2g2000prm ! googlegroups ! com
[Download RAW message or body]

On Aug 29, 3:11 am, pie...@hogranch.com (John R Pierce) wrote:
> On 08/28/11 3:13 AM, pe...@vanroose.be wrote:
>
> > First remark: that's an unusual table design: I would have expected the
> > pivoted form of Table2. Why? Well, every time a row is added to Table1 (DML),
> > a*column*  has to be added to Table2 (DDL).
>
> indeed, that was my initial reaction, it looked like a schema designed
> by a spreadsheet user.   It violates the tenets of relational data design.
>
> If the table was properly structured, the required data could be queried
> with a single JOIN, albeit not in the exact output form shown.
>
> --
> john r pierce                            N 37, W 122
> santa cruz ca                         mid-left coast



Thanks to Peter and Tiger and suggestion from John

Just posting the solution here



 $dbh = DBI->connect($databaseName, $databaseUser, $databasePw)
        or die "Connect failed: $DBI::errstr\n";
 $sth = $dbh->prepare("SELECT Name FROM currency_acc WHERE
currency='SGD'");
 $sth->execute();
 my @colnames = ();
 while (my @names = $sth->fetchrow_array()) { push @colnames,
$names[0]; }


 my $query = 'SELECT ACC';
 foreach (@colnames) { $query .= ', ' . $_; }
 $query .= ' FROM amount_type';

 $sth = $dbh->prepare($query);
 $sth->execute();
 my @names =  @{$sth->{NAME_uc}};
 print "@names \n";
 while (my @r = $sth->fetchrow_array()) { print "@r\n"; }


Thanks
Eddie



[prev in list] [next in list] [prev in thread] [next in thread]