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++;
                        
            }
            }
        }
    }
}
}
}
}
[download]

---

---

Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!

Titles consisting of a single word are discouraged, and in most cases are disallowed outright.

Read Where should I post X? if you're not absolutely sure you're posting in the right place.

Please read these before you post! —

• How do I compose an effective node title?
• How do I post a question effectively?
• Markup in the Monastery

Posts may use any of the Perl Monks Approved HTML tags:

a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr

You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)

	For:		Use:
	&		&
	<		&lt;
	>		&gt;
	[		&#91;
	]		&#93;

Link using PerlMonks shortcuts! What shortcuts can I use for linking?

See Writeup Formatting Tips and other pages linked from there for more info.