| Subject: | error when using same table with different aliases in SQL query |
Hello
When I am using the same table (people.csv) with different aliases (h
and w in my example) in one SQL query, I cannot get the correct result.
Please help me with this.
When I execute the following SQL :
select c.husband,h.age,c.wife,w.age from couple c, people h, people w
where c.husband = h.name and c.wife = w.name
There is a error msg:
DBD::CSV::st execute failed: Error 0 while reading file
D:\Perl\people.csv: at C:/strawberry/perl/site/lib/SQL/Statement.pm
line 813
[for Statement "select c.husband,h.age,c.wife,w.age from couple c,
people h, people w where c.husband = h.name and c.wife = w.name"] at
debug_dbd_csv.pl line 16.
DBD::CSV::st fetchrow_hashref failed: Attempt to fetch row without a
preceeding execute () call or from a non-SELECT statement [for Statement
"select c.husband,h.age,c.wife,w.age from couple c, people h, people w
where c.husband =h.name and c.wife = w.name"] at debug_dbd_csv.pl line 18.
Or sometimes the following error msg:
DBD::CSV::st execute failed: Error 2012 while reading file
D:\Perl\people.csv: EOF - End of data in parsing input stream at
C:/strawberry/perl/site/lib/SQL/Statement.pm line 813
[for Statement "select c.husband,h.age,c.wife,w.age from couple c,
people h, people w where c.husband = h.name and c.wife = w.name"] at
csv.pl line 99.
Execute Err:Error 2012 while reading file D:\Perl\people.csv: EOF - End
of datain parsing input stream at
C:/strawberry/perl/site/lib/SQLStatement.pm line 813
What I am expected is the result as following:
c.husband |h.age |c.wife |w.age |
================================================
James |30 |Mary |22 |
Roger |24 |Lily |26 |
================================================
However, the following SQL queries work well:
select * from couple;
husband |wife |
========================
James |Mary |
Roger |Lily |
========================
Return 2 columns and 2 lines
select * from people;
name |age |
========================
Mary |22 |
Lily |26 |
Roger |24 |
James |30 |
========================
Return 2 columns and 4 lines
select c.husband,h.age from couple c, people h where c.husband = h.name;
c.husband |h.age |
========================
James |30 |
Roger |24 |
========================
Return 2 columns and 2 lines
select c.wife,w.age from couple c, people w where c.wife = w.name;
c.wife |w.age |
========================
Mary |22 |
Lily |26 |
========================
Return 2 columns and 2 lines
I am using Strawberry perl 5.10.1 built for MSWin32-x86-multi-thread.
The workaround would be that I duplicate the people.csv as people2.csv,
and change my sql .
There is the first time I report, so please tell me if there is anything
missing.
| Subject: | couple.csv |
Message body not shown because it is not plain text.
| Subject: | debug_dbd_csv.pl |
#!/usr/bin/perl -w
use strict;
use DBI;
my $dbh = DBI->connect ("dbi:CSV:", "", "", {
f_ext => ".csv",
csv_null => 1,
FetchHashKeyName => 'NAME_lc',
});
my $query = "select c.husband,h.age,c.wife,w.age from couple c, people h, people w where c.husband = h.name and c.wife = w.name";
#my $query = "select c.husband,h.age from couple c, people h where c.husband = h.name ";
my $sth = $dbh->prepare ($query);
$sth->execute ();
while (my $row = $sth->fetchrow_hashref) {
foreach my $col ( keys %$row ) {
print $col,"=",$row->{$col},"\t" if defined $row->{$col};
}
print "done\n\n";
}
$sth->finish ();
| Subject: | people.csv |
Message body not shown because it is not plain text.