Re^2: mysql DBI Nested Queries

[finhagen]

I stumbled onto a reporting chain approach that works, but it's ugly. Looking at your sub process comment, I am wondering if you and the the Monks can help me turn the following code into a sub process?

I replaced the CEO's supervisor field (which was blank) with "CEO" which should allow for a while ( $supervisor ne 'CEO') {}

I tried a couple of approaches but they all ended up in an infinite loop.

Here's the code that works:

our $lname;
our $fname;
our $title;
our $eid;
our $supervisor;
our $supervisor2;
our $supervisor3;
our $supervisor4;
our $supervisor5;
our $supervisor6;
our $supervisor7;
our $supervisor8;
        
#select fname, lname, csg from acnse where supervisor ='pierre.nanterm
+e';
my $sth = $dbh->prepare("SELECT fname, lname, title, supervisor FROM a
+cnse WHERE eid ='employeID';");
$sth->execute or die $sth->errstr;

my $row=23;

        while ( my $aref = $sth ->fetchrow_arrayref)

            {
             my ($fname, $lname, $title, $supervisor) = @$aref;

                #write data to spreadsheet row by row 

                    $worksheet0->write($row++, 2, $fname, $format_HRto
+p2);
                    $worksheet0->write($row++, 2, $lname, $format_HRmi
+ddle);
                    $worksheet0->write($row++, 2, $title, $format_HRmi
+ddle);
                    $worksheet0->write($row++, 2, $supervisor, $format
+_HRbottom);
                    $row++;
            
            $supervisor2 = $supervisor;
            my $sth = $dbh->prepare("SELECT fname, lname, title, super
+visor FROM acnse WHERE eid ='$supervisor2';");
            $sth->execute or die $sth->errstr;
            
            while ( my $aref = $sth ->fetchrow_arrayref)

            {
             my ($fname, $lname, $title, $supervisor2) = @$aref;

                #write data to spreadsheet row by row 

                    $worksheet0->write($row++, 2, $fname, $format_HRto
+p2);
                    $worksheet0->write($row++, 2, $lname, $format_HRmi
+ddle);
                    $worksheet0->write($row++, 2, $title, $format_HRmi
+ddle);
                    $worksheet0->write($row++, 2, $supervisor2, $forma
+t_HRbottom);
                    $row++;
                

            $supervisor3 = $supervisor2;
            my $sth = $dbh->prepare("SELECT fname, lname, title, super
+visor FROM acnse WHERE eid ='$supervisor3';");
            $sth->execute or die $sth->errstr;
            
            while ( my $aref = $sth ->fetchrow_arrayref)

            {
             my ($fname, $lname, $title, $supervisor3) = @$aref;

                #write data to spreadsheet row by row 

                    $worksheet0->write($row++, 2, $fname, $format_HRto
+p2);
                    $worksheet0->write($row++, 2, $lname, $format_HRmi
+ddle);
                    $worksheet0->write($row++, 2, $title, $format_HRmi
+ddle);
                    $worksheet0->write($row++, 2, $supervisor3, $forma
+t_HRbottom);
                    $row++;
            
            $supervisor4 = $supervisor3;
            my $sth = $dbh->prepare("SELECT fname, lname, title, super
+visor FROM acnse WHERE eid ='$supervisor4';");
            $sth->execute or die $sth->errstr;
            
            while ( my $aref = $sth ->fetchrow_arrayref)

            {
             my ($fname, $lname, $title, $supervisor4) = @$aref;

                #write data to spreadsheet row by row 

                    $worksheet0->write($row++, 2, $fname, $format_HRto
+p2);
                    $worksheet0->write($row++, 2, $lname, $format_HRmi
+ddle);
                    $worksheet0->write($row++, 2, $title, $format_HRmi
+ddle);
                    $worksheet0->write($row++, 2, $supervisor4, $forma
+t_HRbottom);
                    $row++;
            
            $supervisor5 = $supervisor4;
            my $sth = $dbh->prepare("SELECT fname, lname, title, super
+visor FROM acnse WHERE eid ='$supervisor5';");
            $sth->execute or die $sth->errstr;
            
            while ( my $aref = $sth ->fetchrow_arrayref)

            {
             my ($fname, $lname, $title, $supervisor5) = @$aref;

                #write data to spreadsheet row by row 

                    $worksheet0->write($row++, 2, $fname, $format_HRto
+p2);
                    $worksheet0->write($row++, 2, $lname, $format_HRmi
+ddle);
                    $worksheet0->write($row++, 2, $title, $format_HRmi
+ddle);
                    $worksheet0->write($row++, 2, $supervisor5, $forma
+t_HRbottom);
                    $row++;
            
            $supervisor6 = $supervisor5;
            my $sth = $dbh->prepare("SELECT fname, lname, title, super
+visor FROM acnse WHERE eid ='$supervisor6';");
            $sth->execute or die $sth->errstr;
            
            while ( my $aref = $sth ->fetchrow_arrayref)

            {
             my ($fname, $lname, $title, $supervisor6) = @$aref;

                #write data to spreadsheet row by row 

                    $worksheet0->write($row++, 2, $fname, $format_HRto
+p2);
                    $worksheet0->write($row++, 2, $lname, $format_HRmi
+ddle);
                    $worksheet0->write($row++, 2, $title, $format_HRmi
+ddle);
                    $worksheet0->write($row++, 2, $supervisor6, $forma
+t_HRbottom);
                    $row++;    
                    
            $supervisor7 = $supervisor6;
            my $sth = $dbh->prepare("SELECT fname, lname, title, super
+visor FROM acnse WHERE eid ='$supervisor7';");
            $sth->execute or die $sth->errstr;
            
            while ( my $aref = $sth ->fetchrow_arrayref)

            {
             my ($fname, $lname, $title, $supervisor7) = @$aref;

                #write data to spreadsheet row by row 

                    $worksheet0->write($row++, 2, $fname, $format_HRto
+p2);
                    $worksheet0->write($row++, 2, $lname, $format_HRmi
+ddle);
                    $worksheet0->write($row++, 2, $title, $format_HRmi
+ddle);
                    $worksheet0->write($row++, 2, $supervisor7, $forma
+t_HRbottom);
                    $row++;    
            
            $supervisor8 = $supervisor7;
            my $sth = $dbh->prepare("SELECT fname, lname, title, super
+visor FROM acnse WHERE eid ='$supervisor8';");
            $sth->execute or die $sth->errstr;
            
            while ( my $aref = $sth ->fetchrow_arrayref)

            {
             my ($fname, $lname, $title, $supervisor8) = @$aref;

                #write data to spreadsheet row by row 

                    $worksheet0->write($row++, 2, $fname, $format_HRto
+p2);
                    $worksheet0->write($row++, 2, $lname, $format_HRmi
+ddle);
                    $worksheet0->write($row++, 2, $title, $format_HRmi
+ddle);
                    $worksheet0->write($row++, 2, $supervisor8, $forma
+t_HRbottom);
                    $row++;
                        
            }
            }
        }
    }
}
}
}
}